Mit Regulären Ausdrücken im Vergleich ist aver MariaDB sehr viel schneller von Raketensilo, 09.11.2020 14:35

Mit Regulären Ausdrücken im Vergleich ist aver MariaDB sehr viel schneller

Raketensilo 09.11.2020 14:35

mysql
php

−1 Informationen zu den Bewertungsregeln

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.

Beitrag melden

−1 Informationen zu den Bewertungsregeln

SELFHTML Forum - Ergänzung zur Dokumentation Übersicht

Raketensilo: Mit Regulären Ausdrücken im Vergleich ist aver MariaDB sehr viel schneller

Beitrag lesen

Mit Regulären Ausdrücken im Vergleich ist aver MariaDB sehr viel schneller

string zerlegen und einzelne Begriffe in Datenbank vergleichen (500)

Input benötigt

Ups. Unfair gemessen!

Mit Regulären Ausdrücken im Vergleich ist aver MariaDB sehr viel schneller

Bessere Beispiele mit preg_quote():