MySQL : Search is incredibly slow when prefixing to_user or from_user with % about homer HOT 2 CLOSED

Add the following columns and indexes ( MySQL ) to your sip_capture table :

resu_ot varchar(100) NOT NULL DEFAULT '',
resu_morf varchar(100) NOT NULL DEFAULT '',
KEY resu_morf (resu_morf),
KEY resu_ot (resu_ot)

Run the following in your database :
DELIMITER |
Create Trigger reverse_field Before insert on sip_capture
FOR EACH ROW
BEGIN
set NEW.resu_ot = reverse( NEW.to_user);
set NEW.resu_morf = reverse( NEW.from_user );
END;
|
DELIMITER ;

And apply the following patch :
diff --git a/webhomer/DataTable/sip/SipSearchService.php b/webhomer/DataTable/sip/SipSearchService.php
index e41ec68..0e41f53 100644
--- a/webhomer/DataTable/sip/SipSearchService.php
+++ b/webhomer/DataTable/sip/SipSearchService.php
@@ -129,7 +131,15 @@ class SipSearchService implements ISipService
if(!isset($callwhere)) $callwhere = "(";
if($key == "callid" && $b2b ) $callwhere.=" (";

•    if ( in_array( $key, arraY('from_user', 'to_user' )) && $value[0] === '%' )
  

•    {
  

•         $key = strrev( $key );
  

•         $value = strrev( $value );
  

•    }
  

  • $eqlike = preg_match("/%/", $value) ? " like " : " = ";

      if(preg_match("/^!/", $value)) {
          $value =  preg_replace("/^!/", "", $value);
  

Here is the patch as a base64 :

ZGlmZiAtLWdpdCBhL3dlYmhvbWVyL0RhdGFUYWJsZS9zaXAvU2lwU2VhcmNoU2VydmljZS5waHAg
Yi93ZWJob21lci9EYXRhVGFibGUvc2lwL1NpcFNlYXJjaFNlcnZpY2UucGhwCmluZGV4IGU0MWVj
NjguLjBlNDFmNTMgMTAwNjQ0Ci0tLSBhL3dlYmhvbWVyL0RhdGFUYWJsZS9zaXAvU2lwU2VhcmNo
U2VydmljZS5waHAKKysrIGIvd2ViaG9tZXIvRGF0YVRhYmxlL3NpcC9TaXBTZWFyY2hTZXJ2aWNl
LnBocApAQCAtMTI5LDcgKzEzMSwxNSBAQCBjbGFzcyBTaXBTZWFyY2hTZXJ2aWNlIGltcGxlbWVu
dHMgSVNpcFNlcnZpY2UKIAkgICBpZighaXNzZXQoJGNhbGx3aGVyZSkpICRjYWxsd2hlcmUgPSAi
KCI7CiAJICAgaWYoJGtleSA9PSAiY2FsbGlkIiAmJiAkYjJiICkgJGNhbGx3aGVyZS49IiAoIjsK
IAorICAgICAgICBpZiAoIGluX2FycmF5KCAka2V5LCBhcnJhWSgnZnJvbV91c2VyJywgJ3RvX3Vz
ZXInICkpICYmICR2YWx1ZVswXSA9PT0gJyUnICkKKyAgICAgICAgeworICAgICAgICAgICAgICRr
ZXkgPSBzdHJyZXYoICRrZXkgKTsKKyAgICAgICAgICAgICAkdmFsdWUgPSBzdHJyZXYoICR2YWx1
ZSApOworICAgICAgICB9CisKIAkgICAkZXFsaWtlID0gcHJlZ19tYXRjaCgiLyUvIiwgJHZhbHVl
KSA/ICIgbGlrZSAiIDogIiA9ICI7CiAgICAgIAogICAgICAgICAgICBpZihwcmVnX21hdGNoKCIv
XiEvIiwgJHZhbHVlKSkgewogICAgICAgICAgICAgICAgJHZhbHVlID0gIHByZWdfcmVwbGFjZSgi
L14hLyIsICIiLCAkdmFsdWUpOwo=

On the tested machine, the search time for a query prefixed with % took several minutes. With the patch, it took less than 2 seconds. Test machine had 70,000,000 rows in the table.

from homer.

Thanks for the patch, but we couldn't accept it.
first, you should understand if you use % (wildcard) as prefix, mysql don't use index at all and make full scan on the table/tables and of course it takes a lot time (depends how much records you have).
second, to avoid such behavior, better use predefined prefixes and MYSQL SQL query with "IN (array)" i.e. (123456 will search +123456, 00123456, 023456 etc)
such query will use index and will be more effective than use reverse value and create additional two indexes.

btw, please use Homer5. There we use more effective sql schema and design.

from homer.