string zerlegen und einzelne Begriffe in Datenbank vergleichen (500)

[Bigna]

Ich habe folgende Aufgabe:

Ein beliebiger Fließtext maximal 1000 Wörter, inkl. Kommas etc., soll verglichen werden mit einer Kategorienliste (diese besteht aus 500 Kategorien die in einer mysql Datenbank gespeichert werden. Mein erster Ansatz ist der.

1. Ich nehme den Fließtext, zerlege ihn in ein Array.
2. Lösche Satzzeichen und Leerzeichen
3. Lösche alle doppelten Einträge.
4. Lösche alle Begleitworte, wie der, die, das, wer und etc.

Dann habe ich das Ganze mit eine Test/Beispiel Fließtexten mit 1000 Wörtern getestet Im Durchschnitt blieben dann aus einem Text von 1000 Wörter ca. 50 Prozent über. Was aber immer noch 500 Wörter sind. Jetzt würde ich über eine SELECT Abfrage die gefilterten Wörter mit der Kategorienliste vergleichen.

Mein großes Bedenken ist aber, das ich für jede Kategorie eine SELCT Abfrage starten muss, also 500 hintereinander? Ich weiß nicht ob das die Lösung ist? Meine Frage nun, und Bitte an die Intelligenz und den Einfallsreichtum des verschiedenen Kreativen Menschen.

Kann ich die SELCT Abfragen vereinfachen, zusammenfassen, oder gibt es gar einen anderen besseren Ansatz um diese Aufgabe zu bewältigen.

[Rolf B]

Hallo Bigna,

du kannst eine IN Klausel konstruieren.

D.h. du hast dein Array mit Suchwörtern. Die behandelst Du erstmal vor, so sie für den DB Kontext korrekt escaped sind (PDO::quote oder mysqli::escape_string).

$inClause = "('" . implode("', '", $keywords) . "')";

$sql = "SELECT ... FROM keywords WHERE keyword in $inClause";

Das kann immer noch dauern und deine Keywords-Tabelle sollte einen Index auf der keyword-Spalte haben, aber es ist immerhin nur eine Query.

Probier's aus; wenn das zu langsam ist, muss man schauen, ob das in-memory schneller geht. Vermutlich nicht.

Rolf

[Raketensilo]

Ein beliebiger Fließtext maximal 1000 Wörter, inkl. Kommas etc., soll verglichen werden mit einer Kategorienliste (diese besteht aus 500 Kategorien die in einer mysql Datenbank gespeichert werden.

Ich lese, Du hast einen Fließtext und 500 Begriffe in einer Datenbank.

Dann ist das womöglich (abhängig von der Aufgabe) nicht so unbedingt ein Job für die Datenbank.

Ich habe folgende Aufgabe

Ich kann keine Aufgabe lösen (oder dabei helfen), so langeich keine vernünftige Bechreibung habe. "soll verglichen werden" ist viel zu wenig. Was soll denn GENAU passieren?

[Raketensilo]

Nun, da meine Antwort nicht jedem gefallen hat, mangelt es wohl an einem anschaulichen und trivialem Beispiel:

1. Testumgebung

Zunächst brauche ich einmal 500 Wörter und einmal 1000 Wörter. Ich missbraucne mangels Phantasie und Lust dafür eine der gängigen Passwortlisten:

wget https://raw.githubusercontent.com/danielmiessler/SecLists/master/Passwords/Common-Credentials/10-million-password-list-top-1000000.txt

und erzeuge zwei Dateien. Eine mit 500 (das ist die vorgestellte Anzahl der Kategorien aus der Datenbank) Wörtern aus der Liste. Und eine mit 1000 Wörtern (so wurde der EINE Vergleichstext beschrieben, über dessen Herkunft - anders als bei den „Kategorien“ keine Aussage getroffen wurde, wobei aber die Beschreibung die Deutung zulässt, dass dieser aber nicht in der Datenbank ist). Mindestens 2 bis 15 Wörter kommen in den "Kategorien" und im "Text" vor. Das hab ich mal als Annahme für den Test untersellt:

<?php

$ar = file( '/tmp/10-million-password-list-top-1000000.txt' ); 
$last = count( $ar );
for ( $i=0; $i<$last; $i++ ) {
	$s = trim( $ar[$i] );
	if ( $s == '' ) {
		unset( $ar[$i] );
	} else {
		$ar[$i] = $s;	
    }	
}

$keys = array_rand( $ar, 500 );

$ar500 = [];

foreach ( $keys as $k ) {
   $ar500[] = $ar[$k];
}

unset( $ar );

file_put_contents ( '500.json', json_encode( $ar500 ) );

$z = rand(2, 15);


$keys = array_rand( $ar500, $z );

$ar=[];
foreach( $keys as $k ) {
	$ar[] = $ar500[$k];
}

$alpha='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
$l = strlen( $alpha );

for( $k=$z+1; $k<=1000; $k++ ) {
	$z = rand(5,15);
	$w='';
        for ( $i = 0; $i<=$z; $i++) {
		$w .= substr ( $alpha , rand( 0,$l-1 ), 1 );
	}
	$ar[] = $w;
}

shuffle( $ar );
file_put_contents( '1000.txt', implode( ' ', $ar ) );

Ich behaupte ausdrücklich nicht, dass der obige Code elegant, richtungsweisend oder gar „der einzig Wahre“ sei. Für den Test genügt er aber: Die Datei '1000.txt' mit den 1000 Wörtern enthält nach dem Ausführen zwischen 0 und 10 der Kategorien, sonst irgendwelche "Wörter" aus Buchstaben und Ziffern, 5 bis 15 Zeichen lang. Das Erzeugen dauert auf meinem nicht gar so schnellen Rechner übrigens in etwa 1 Sekunde.

2. Eine von vielen "Lösungen"

Nachdem ich nun diese beiden Dateien habe, werde ich die Datei mit mit den 500 zu vergleichenden Begriffen ("Kategorien") einfach mal vom Dateisystem öffnen und als JSON Parsen lassen - statt aus der Datenbank zu lesen. Das dürfte "halbwegs" egal sein…

Dann nehme ich den String mit 1000 Wörtern aus der Textdatei, implodiere den zu einem Array und dann mache ich einen Vergleich.

Ich habe nicht umsonst gefragt, was der Vergleich bringen soll. Denn das kann alles mögliche sein - aber so lange ich keine Nachricht bekomme, mache ich einen einfachen und stumpfen Vergleich auf vollständige übereinstimmung:

<?php

$w1000 = explode( ' ', file_get_contents( '1000.txt' ) );
$w500 = json_decode( file_get_contents( '500.json' ) );


foreach ( $w500 as $k ) {
	$helper[ $k ] = true;
}


$founds=[];

foreach ( $w1000 as $w ) {
	if ( array_key_exists ( $w, $helper ) ) {
		$founds[]=$w;
	}
}

print_r( $founds );

Ich behaupte ausdrücklich nicht, dass der obige Code elegant, richtungsweisend oder gar der einzig Wahre sei. Für den Test genügt er aber.

3. Test und Zeiten:

Und jetzt sehen wir mal nach, ob mein Vorgehen "geeignet" ist:

/tmp$ php 1.php ; time php 2.php

Array
(
    [0] => wasteoftime
    [1] => dcrpce
    [2] => volodya
    [3] => mohen
    [4] => stfu
    [5] => camcam
    [6] => wcamwr
    [7] => catsterr
    [8] => q
)

real	0m0,030s
user	0m0,022s
sys	  0m0,009s

Hinweis: In den 0m0,030s ist der Start vom PHP als CLI mit drin… Auf einem Server mit PHP als FastCGI oder Modul würde das sicherlich schneller laufen.

Was aber fest steht: Mit der Nutzung einer Datenbank und ggf. mehreren Abfragen ist eine solche Geschwindigkeit definitiv nicht zu erwarten. Durch den nachvollziehbaren Test ist die Richtigkeit meines Satzes "Dann ist das womöglich (abhängig von der Aufgabe) nicht so unbedingt ein Job für die Datenbank." bewiesen.

Auch mein Hinweis auf das Fehlen einer vernünftigen Bechreibung hinsichtlich der Aufgabe geht nicht fehl, weil bei anderen Aufgaben natürlich ein anderes Vorgehen sinnvoll sein kann.

4. Anmerkung:

Natürlich kann hier mit negativen Bewertungen um sich hauen… Ich würde demjenigen sehr ernsthaft was ganz anderes empfehlen. Nämlich sein Verhalten zu überprüfen.

[dedlfix]

Tach!

4. Anmerkung:

Natürlich kann hier mit negativen Bewertungen um sich hauen… Ich würde demjenigen sehr ernsthaft was ganz anderes empfehlen. Nämlich sein Verhalten zu überprüfen.

Ich empfehle dir, dein Verhalten zu überprüfen, und nicht bei jeder negativen Bewertung an die Decke zu gehen.

dedlfix.

[Raketensilo]

Ich empfehle dir, dein Verhalten zu überprüfen, und nicht bei jeder negativen Bewertung an die Decke zu gehen.

Ich jedenfalls gehe jetzt zu meiner Freundin. Nicht an, sondern unter die Decke. 😀

[Rolf B]

Hallo Raketensilo,

ich habe mal die Gegenprobe mit einer IN-Klausel aus 1000 Wörtern gemacht.

Der Aufbau der Kategorien-Tabelle mit 500 Wörtern dauerte ewig, ich hatte aber keinen Lust, nachzulesen, wie man einen Bulk-Load macht.

Die Abfrage dauert dann 5ms. Ohne den Start von PHP, ich habe die Laufzeit mittels hrtime im PHP bestimmt.

Witzigerweise ging das SQL schneller, wenn ich den Primary Key auf die Kategorien gedroppt habe. Indexe helfen nicht immer.

Rolf

[Raketensilo]

Hm. Dann mach ich mal eine vergleichsfähige Gegenprobe

<?php

$w1000 = explode( ' ', file_get_contents( '1000.txt' ) );

$startT = hrtime( true );

$w500 = json_decode( file_get_contents( '500.json' ) );

$helper = [];
foreach ( $w500 as $k ) {
	$helper[ $k ] = true;
}

echo "Lesen der 500 Wörter  : " . ( hrtime( true ) - $startT ) / 1e+6 . " ms.\n";

$startT = hrtime( true );

$founds=[];
foreach ( $w1000 as $w ) {
	if ( array_key_exists ( $w, $helper ) ) {
		$founds[] = $w;
	}
}

echo "Vergleich mit aus 1000: " . ( hrtime( true ) - $startT ) / 1e+6 . " ms.\n\n";

#print_r( $founds );

php 2.php
Lesen der 500 Wörter  : 0.133038 ms.
Vergleich mit aus 1000: 0.119373 ms.

Da sah mit dem antiken microtime(true) nicht anders aus.

Ein bulk-insert ist eigentlich einfach:

INSERT INTO `tabelle` (`Kategorien`)'
VALUES
 ("Wert"),
 ("anderer Wert")
;

Für meine Vergleichsdaten mach ich das so:

<?PHP
# create table `test`.`kategorien` (`Kategorie` VARCHAR(30) NOT NULL );

$w500 = json_decode( file_get_contents( '500.json' ) );

include 'settings.php';
$dbh = new PDO(
  "mysql:host=localhost;dbname=$database",
   $user,
   $pass
);
$dbh -> query( 'DELETE FROM `kategorien`' );

for($i=0; $i<count( $w500 ); $i++) {
	$w500[$i] = '(' . $dbh->quote( $w500[$i] ) . ')';
}

$sql = "INSERT INTO `test`.`kategorien`(`Kategorie`)
  VALUES " . implode(',', $w500 ) .';';

$startT = hrtime( true );
$dbh -> query( $sql );
echo "Einlesen der 500 Wörter in die Datentabelle: " 
   . ( hrtime( true ) - $startT ) / 1e+6 
   . " ms.\n";

Resultat:

Einlesen der 500 Wörter als Bulk-Insert in die Datentabelle: 7.429675 ms

(Diese Zahl interessiert nicht - aber das ist nicht mehr "ewig" 😀)

Kommen wir zur Abfrage:

<?php
# Tabelle angelegt mit:
# create table `test`.`kategorien`
# (`Kategorie` VARCHAR(30) NOT NULL );


# set $database, $user and $pass
include 'settings.php'; 
$dbh = new PDO( 
	"mysql:host=localhost;dbname=$database",
	$user,
	$pass
);

# Frisch einlesen?
/*
$dbh -> query( 'DELETE FROM `kategorien`' );

$w500 = json_decode(
   file_get_contents( '500.json' )
);

for($i=0; $i<count( $w500 ); $i++) {
	$w500[$i] = '(' . $dbh->quote( $w500[$i] ) . ')';
}

$sql = "INSERT INTO `test`.`kategorien`(`Kategorie`) VALUES "
. implode(',', $w500 );

$startT = hrtime( true );
$dbh -> query( $sql );
echo "Einlesen der 500 Wörter in die Datentabelle:       "
. ( hrtime( true ) - $startT ) / 1e+6 . " ms.\n";
#*/

$startT = hrtime( true );
$w1000 = explode( ' ', file_get_contents( '1000.txt' ) );
for($i=0; $i<count( $w1000 ); $i++) {
	$w1000[$i] = $dbh->quote( $w1000[$i] );
}


$sql = "SELECT `Kategorie` FROM `test`.`kategorien`
       WHERE `Kategorie`
       IN (" . implode(',', $w1000 ) . ")";

$sth = $dbh->prepare( $sql );
$sth->execute();

$founds=[];
while ( $result = $sth->fetch( PDO::FETCH_ASSOC ) ) {
	$founds[] = $result['Kategorie'];
}

echo "Ermitteln der Übereinstimmungen aus der Datenbank: "
. ( hrtime( true ) - $startT ) / 1e+6
. " ms.\n";

#print_r( $founds );

Witzigerweise ging das SQL schneller, wenn ich den Primary Key auf die Kategorien gedroppt habe. Indexe helfen nicht immer.

Das ist für kurze Tabellen bekannt. Aber, dass Indexe eine Abfrage verlangsamen habe ich noch nie gehört. Kann es sein, dass die Antwort aus einem Cache des DBMS kam? Bei mir ist das so:

Erster Start:

Ermitteln der Übereinstimmungen aus der Datenbank: 5.63705 ms.

Zweiter Start:

Ermitteln der Übereinstimmungen aus der Datenbank: 1.010887 ms.

Wenn ich die Tabelle leere und neu einlese sind es nämlich wieder 5-7 ms... Aufmerksame Leser werden aber gemerkt haben, dass in PHP der reine Vergleich 0,12 ms gedauert hat… Ich sollte erwähnen, dass die Datenbank auf dem localhost liegt und via socket befragt wird. Geht das über das Netz kommt schon wegen Paketlaufzeit noch etwas hinzu. Das kann aber auch für Dateisysteme gelten (nfs-mount?).

Aber die Ergebnisse stützen meine Aussage, dass, abhängig von der Aufgabe, eine Lösung des Vergleiches außerhalb der Datenbank sinnvoll sein kann.

[Raketensilo]

Habe gerde gesehen, ich habe unfair gemessen - und zwar zum Nachteil der Datenbank (Zeilen 37,38 mussten getauscht werden, weil das Einlesen der 1000 Wörter nicht zum vergleich gehört…)

<?php
# Tabelle angelegt mit:
# create table `test`.`kategorien`
# (`Kategorie` VARCHAR(30) NOT NULL );


# set $database, $user and $pass
include 'settings.php'; 
$dbh = new PDO( 
	"mysql:host=localhost;dbname=$database",
	$user,
	$pass
);

# Frisch einlesen?
/*
$dbh -> query( 'DELETE FROM `kategorien`' );

$w500 = json_decode(
   file_get_contents( '500.json' )
);

for($i=0; $i<count( $w500 ); $i++) {
	$w500[$i] = '(' . $dbh->quote( $w500[$i] ) . ')';
}

$sql = "INSERT INTO `test`.`kategorien`(`Kategorie`) VALUES "
. implode(',', $w500 );

$startT = hrtime( true );
$dbh -> query( $sql );
echo "Einlesen der 500 Wörter in die Datentabelle:       "
. ( hrtime( true ) - $startT ) / 1e+6 . " ms.\n";
#*/


$w1000 = explode( ' ', file_get_contents( '1000.txt' ) );
$startT = hrtime( true );
for($i=0; $i<count( $w1000 ); $i++) {
	$w1000[$i] = $dbh->quote( $w1000[$i] );
}


$sql = "SELECT `Kategorie` FROM `test`.`kategorien`
       WHERE `Kategorie`
       IN (" . implode(',', $w1000 ) . ")";

$sth = $dbh->prepare( $sql );
$sth->execute();

$founds=[];
while ( $result = $sth->fetch( PDO::FETCH_ASSOC ) ) {
	$founds[] = $result['Kategorie'];
}

echo "Ermitteln der Übereinstimmungen aus der Datenbank: "
. ( hrtime( true ) - $startT ) / 1e+6
. " ms.\n";

#print_r( $founds );

Ergebnisse:

Erster Start:

Ermitteln der Übereinstimmungen aus der Datenbank: 4.055074 ms.

Zweiter Start (Daten aus dem Abfragecache von MySQL):

Ermitteln der Übereinstimmungen aus der Datenbank: 0.945233 ms.

Aber die Aussage muss ich nicht ändern, denn in reinem PHP waren es:

php 2.php
Lesen der 500 Wörter  : 0.133038 ms.
Vergleich mit aus 1000: 0.119373 ms.

[Raketensilo]

Der Test läuft darauf hinaus, die 1000 Wörter gegen die 500 Kategorien mit einem Regulären Ausdruck, der aus zu vergleichen. Als Beispiel, wie das aussieht, zeige ich den gekürzten SQL-Befehl:

SELECT `Kategorie` FROM `test`.`kategorien`
       WHERE `Kategorie` REGEXP 'YCk34LVWj$|uQh6vKHCj$|iFYvsBGr1z$|6WNYLyby$|uxHjkamqODdmf$|LBJL8P4D6g$|T5...

Der Test:

<?php
# Tabelle angelegt mit:
# create table `test`.`kategorien`
# (`Kategorie` VARCHAR(30) NOT NULL );

function maskiere( $s ) {
	$chars2mask  = [ '\''  , '/' , '*'  ,'?'  ,'+' , '.' , '^' , '$' , '[' , ']' , '|'  ];
	$maskedchars = [ '\\\'', '\/', '\*' ,'\?' ,'\+', '\.', '\^', '\$', '\[', '\]', '\|' ];
	return str_replace( $chars2mask, $maskedchars, $s );	
}


# set $database, $user and $pass
include 'settings.php'; 
$dbh = new PDO( 
	"mysql:host=localhost;dbname=$database",
	$user,
	$pass
);

# Frisch einlesen?
#/*
$dbh -> query( 'DELETE FROM `kategorien`' );

$w500 = json_decode(
   file_get_contents( '500.json' )
);

for($i=0; $i<count( $w500 ); $i++) {
	$w500[$i] = '(' . $dbh->quote( $w500[$i] ) . ')';
}

$sql = "INSERT INTO `test`.`kategorien`(`Kategorie`) VALUES "
. implode(',', $w500 );

$startT = hrtime( true );
$dbh -> query( $sql );
echo "Einlesen der 500 Wörter in die Datentabelle:       "
. ( hrtime( true ) - $startT ) / 1e+6 . " ms.\n";
#*/

$startT1 = hrtime( true );
$w1000 = explode( ' ', file_get_contents( '1000.txt' ) );
$startT = hrtime( true );
for($i=0; $i<count( $w1000 ); $i++) {
	$regex[] = maskiere( $w1000[$i] ) . '$';
}
$regex = implode( '|', $regex );
$regex = $dbh->quote( $regex );
$endT1 = hrtime( true );

$startT2 = hrtime( true );
$sql = "SELECT `Kategorie` FROM `test`.`kategorien`
       WHERE `Kategorie` REGEXP "
       . $regex;

$sth = $dbh->prepare( $sql );
$sth->execute();

$founds=[];
while ( $result = $sth->fetch( PDO::FETCH_ASSOC ) ) {
	$founds[] = $result['Kategorie'];
}

$endT2 = hrtime( true );
echo "Zeit zum Erzeugen des regulären Ausdrucks: "
. ( $endT1 - $startT ) / 1e+6
. " ms.\n";
echo "Zeit zum Ermitteln der Übereinstimmungen aus der Datenbank: "
. ( $endT2 - $startT2 ) / 1e+6
. " ms.\n";
echo "Gesamtzeit: "
. ( $endT2 - $startT1 ) / 1e+6
. " ms.\n";
echo substr( $sql , 0, 150 ) . "...\n";


print_r( $founds );

• Mit dem Reqex und ohne Abfragecache braucht das MySQL bei mir um die 50-60 ms.
• Kann der Abfragecache genutzt werden (Tabelle und Befehle unverändert, Ergebnis noch im Cache), so sinkt die Zeit auf rund 2ms.
• Der "Hammer" ist aber, dass MariaDB (Version: 10.3.25-MariaDB-0+deb10u1) scheinbar sogar Regexe wie '^foo$' wegoptimiert - denn mit diesem Regex (der den gesamten String vergleicht) dauerte die Abfrage nur ewa 10ms.

In purem PHP dauerte der Vorgang hingegen rund 270ms:

<?php

class Searcher {
	
	protected $ar;
	protected $helper;

	function __construct () {
		$ar = json_decode( file_get_contents( '500.json' ) );
		foreach ( $ar as $k ) {
			$helper[$k]=true;
		}
		$this->ar     = $ar;
		$this->helper = $helper;
	}
	
	function maskiere( $s ) {
		$chars2mask  = ['\''  , '/' , '*'  ,'?'  ,'+' , '.' , '^' , '$' , '[' , ']' , '|'  ];
		$maskedchars = ['\\\'', '\/', '\*' ,'\?' ,'\+', '\.', '\^', '\$', '\[', '\]', '\|' ];
		return str_replace( $chars2mask, $maskedchars, $s );	
	}
	
	function start_with_regex( $s ) {
		$pattern = '/^' . $this->maskiere( $s ) . '/';
		foreach ( $this->ar as $haystack ) {
			if ( preg_match( $pattern, $haystack ) ) return true;
			#if ( 0===strpos( $haystack, $s ) ) return true;
		}
	}
	
	function end_with_regex( $s ) {
		#$l = strlen( $s );
		$pattern = '/' . $this->maskiere( $s ) . '$/';
		foreach ( $this->ar as $haystack ) {
			if ( preg_match( $pattern, $haystack ) ) return true;
		}
	}	
	
	function contains_regex( $s ) {
		$pattern = '/' . $this->maskiere( $s ) . '/';
		foreach ( $this->ar as $haystack ) {
			if ( preg_match( $pattern, $haystack ) ) return true;
		}
	}


	function contains_exact( $s ) {
		if ( array_key_exists( $s, $this->helper ) ) return true;
	}	
	
	function start_with_str( $s ) {
		foreach ( $this->ar as $haystack ) {
			if ( 0 === strpos( $haystack, $s ) ) return true;
		}
	}
	
	function end_with_str( $s ) {
		$l = strlen( $s );
		foreach ( $this->ar as $haystack ) {
			$hl = strlen( $haystack );
			if ( ( $l - $hl ) === strpos( $haystack, $s ) ) return true;
		}
	}	
	
	function contains_str( $s ) {
		foreach ( $this->ar as $haystack ) {
			$p = strpos( $haystack, $s );
			if ( 0 === $p OR $p ) return true;
		}
	}
	
	function get_helper() {
		return $this->helper;
	}
	
}


$searcher = new Searcher();

$w1000 = explode(
  ' ',
  file_get_contents( '1000.txt' )
);

### contains_exact
$founds = [];
$helper = $searcher->get_helper();
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		array_key_exists( $w, $helper )
	) $founds[] = $w;	
}
$endT = hrtime( true );
echo "contains_exact  : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";

### contains_regex:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> contains_regex( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "contains_regex  : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";

### contains_str:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> contains_str( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "contains_str    : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";


### start_with_regex:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> start_with_regex( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "start_with_regex: "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";

### start_with_str:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> start_with_str( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "start_with_str  : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";


### end_with_regex:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> end_with_regex( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "end_with_regex  : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";


### end_with_str:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> end_with_str( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "end_with_str    : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";

print_r( $founds );

php 4.php
contains_exact  : 0.121456 ms. 14 Funde.
contains_regex  : 234.837984 ms. 14 Funde.
contains_str    : 261.173564 ms. 14 Funde.
start_with_regex: 230.275251 ms. 14 Funde.
start_with_str  : 188.09143 ms. 14 Funde.
end_with_regex  : 234.157274 ms. 14 Funde.
end_with_str    : 239.649441 ms. 14 Funde.

Diskussion:

Wenn eine genaue Übereinstimmung gesucht wird, ist reines PHP überlegen, sonst "um Klassen schlechter". Getestet wurde mit einer sich stark langweilenden lokalen Mariadb-Installation. Verschiebungen zuungunsten der Datenbank können sich bei einer sehr langsamen Datenbankverbindung, Belastung des Datenbankservers oder einem anderen DBMS ergeben.

[Rolf B]

Hallo Raketensilo,

du scheinst eine Menge Corona-Leerlauf zu haben 😉

Oder sehr an Optimierungsaufgaben interessiert zu sein. Jedenfalls danke für das Teilen deiner Erkenntnisse.

Rolf

[Raketensilo]

du scheinst eine Menge Corona-Leerlauf zu haben 😉

Ja.

Oder sehr an Optimierungsaufgaben interessiert zu sein.

Jepp. Ich kauf ja nicht einen neuen Computer nur weil eine Aufgabe bei ungünstiger Lösung zu lange dauert…

Jedenfalls danke für das Teilen deiner Erkenntnisse.

Ich glaube immer, dass andere genau so neugierig sind.

[Raketensilo]

Bessere Beispiele mit preg_quote():

<?php
# Tabelle angelegt mit:
# create table `test`.`kategorien`
# (`Kategorie` VARCHAR(30) NOT NULL );

# set $database, $user and $pass
include 'settings.php'; 
$dbh = new PDO( 
	"mysql:host=localhost;dbname=$database",
	$user,
	$pass
);

# Frisch einlesen?
#/*
$dbh -> query( 'DELETE FROM `kategorien`' );

$w500 = json_decode(
   file_get_contents( '500.json' )
);

for($i=0; $i<count( $w500 ); $i++) {
	$w500[$i] = '(' . $dbh->quote( $w500[$i] ) . ')';
}

$sql = "INSERT INTO `test`.`kategorien`(`Kategorie`) VALUES "
. implode(',', $w500 );

$startT = hrtime( true );
$dbh -> query( $sql );
echo "Einlesen der 500 Wörter in die Datentabelle:       "
. ( hrtime( true ) - $startT ) / 1e+6 . " ms.\n";
#*/

$startT1 = hrtime( true );
$w1000 = explode( ' ', file_get_contents( '1000.txt' ) );
$startT = hrtime( true );
for($i=0; $i<count( $w1000 ); $i++) {
	$regex[] = preg_quote( $w1000[$i] ) . '$';
}
$regex = implode( '|', $regex );
$regex = $dbh->quote( $regex );
$endT1 = hrtime( true );

$startT2 = hrtime( true );
$sql = "SELECT `Kategorie` FROM `test`.`kategorien`
       WHERE `Kategorie` REGEXP "
       . $regex;

$sth = $dbh->prepare( $sql );
$sth->execute();

$founds=[];
while ( $result = $sth->fetch( PDO::FETCH_ASSOC ) ) {
	$founds[] = $result['Kategorie'];
}

$endT2 = hrtime( true );
echo "Zeit zum Erzeugen des regulären Ausdrucks: "
. ( $endT1 - $startT ) / 1e+6
. " ms.\n";
echo "Zeit zum Ermitteln der Übereinstimmungen aus der Datenbank: "
. ( $endT2 - $startT2 ) / 1e+6
. " ms.\n";
echo "Gesamtzeit: "
. ( $endT2 - $startT1 ) / 1e+6
. " ms.\n";
echo substr( $sql , 0, 150 ) . "...\n";


print_r( $founds );

<?php

class Searcher {
	
	protected $ar;
	protected $helper;

	function __construct () {
		$ar = json_decode( file_get_contents( '500.json' ) );
		foreach ( $ar as $k ) {
			$helper[$k]=true;
		}
		$this->ar     = $ar;
		$this->helper = $helper;
	}
	

	function start_with_regex( $s ) {
		$pattern = '/^' . preg_quote( $s . '/' );
		foreach ( $this->ar as $haystack ) {
			if ( preg_match( $pattern, $haystack ) ) return true;
			#if ( 0===strpos( $haystack, $s ) ) return true;
		}
	}
	
	function end_with_regex( $s ) {
		#$l = strlen( $s );
		$pattern = '/' . preg_quote( $s . '$/' );
		foreach ( $this->ar as $haystack ) {
			if ( preg_match( $pattern, $haystack ) ) return true;
		}
	}	
	
	function contains_regex( $s ) {
		$pattern = '/' . preg_quote( $s . '/' );
		foreach ( $this->ar as $haystack ) {
			if ( preg_match( $pattern, $haystack ) ) return true;
		}
	}


	function contains_exact( $s ) {
		if ( array_key_exists( $s, $this->helper ) ) return true;
	}	
	
	function start_with_str( $s ) {
		foreach ( $this->ar as $haystack ) {
			if ( 0 === strpos( $haystack, $s ) ) return true;
		}
	}
	
	function end_with_str( $s ) {
		$l = strlen( $s );
		foreach ( $this->ar as $haystack ) {
			$hl = strlen( $haystack );
			if ( ( $l - $hl ) === strpos( $haystack, $s ) ) return true;
		}
	}	
	
	function contains_str( $s ) {
		foreach ( $this->ar as $haystack ) {
			$p = strpos( $haystack, $s );
			if ( 0 === $p OR $p ) return true;
		}
	}
	
	function get_helper() {
		return $this->helper;
	}
	
}


$searcher = new Searcher();

$w1000 = explode(
  ' ',
  file_get_contents( '1000.txt' )
);

### contains_exact
$founds = [];
$helper = $searcher->get_helper();
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		array_key_exists( $w, $helper )
	) $founds[] = $w;	
}
$endT = hrtime( true );
echo "contains_exact  : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";

### contains_regex:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> contains_regex( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "contains_regex  : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";

### contains_str:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> contains_str( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "contains_str    : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";


### start_with_regex:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> start_with_regex( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "start_with_regex: "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";

### start_with_str:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> start_with_str( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "start_with_str  : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";


### end_with_regex:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> end_with_regex( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "end_with_regex  : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";


### end_with_str:
$founds=[];
$startT = hrtime( true );
foreach ( $w1000 as $w ) {
	if ( 
		$searcher -> end_with_str( $w )
	) $founds[] = $w;
}
$endT = hrtime( true );
echo "end_with_str    : "
. ( $endT - $startT ) / 1e+6
. " ms. " . count($founds) . " Funde.\n";

print_r( $founds );