LIKE search slow

126 Posts

johnrobert27 Reply #1, 13 years, 2 months ago

hi there!

Problem:

I have created a search which runs in mysql 'LIKE' method it compose of around 6 or 7 tables
but this method is really slow in fetching data.

Unfortunately I will stick with this method until the very of our developing, so I'm asking for your help what would be the problem with this method?

thank you very much in advance!

81 Posts

Dunnock Reply #2, 13 years, 2 months ago

Is the columns you use for like indexed? Does the query have this kind of like '%search%' <-- percentage marks around the word? if it has, then it cannot use indexes for that column. I am assuming you use MySQL, it only can use indexes with 'search%' <-- only suffix percentage mark.

How many rows of data and stuff. also could help if you show the code

126 Posts

johnrobert27 Reply #3, 13 years, 2 months ago

thanks for the reply

yeah your right im using this kind of search '%keyword%' also im using MySQL

here's my code

$query = $modx->newQuery('Tblbook');
        $query->select(array (
            'Tblbook.system_product_id',
            'Tblbook.system_brand_id',
            'Tblbook.brand_name',
            'Tbooks.content',
            'Tblbook.company',
            'Tblbook.form_class',
            'Tgen.system_generic_id',
            'Tsec1.section1_id',
            'Tsec2.section2_id',
            'Tsec3.section3_id',
            'Tsec4.section4_id'
 
        ));
    
        $query->innerJoin('Tblbooks', 'Tbooks', array("Tblbook.system_brand_id = Tbooks.system_brands_id")); 
        $query->innerJoin('Tblbrandgenericgroupsms','Tgen',array("Tblbook.system_brand_id = Tgen.system_brand_id"));
        $query->innerJoin('Tblproductsections', 'Tprodsec', array(" Tblbook.system_product_id = Tprodsec.system_product_id")); 
        $query->leftJoin('Tblsection', 'Tsec', array(" Tprodsec.system_section_id = Tsec.system_section_id")); 
        $query->leftJoin('Tblsection1', 'Tsec1', array(" Tsec.system_section1_id = Tsec1.system_section1_id"));
        $query->leftJoin('Tblsection2', 'Tsec2', array(" Tsec.system_section2_id = Tsec2.system_section2_id"));
        $query->leftJoin('Tblsection3', 'Tsec3', array(" Tsec.system_section3_id = Tsec3.system_section3_id"));
        $query->leftJoin('Tblsection4', 'Tsec4', array(" Tsec.system_section4_id = Tsec4.system_section4_id"));
        $query->where(array(
        array(
            "Tblbook.brand_name:LIKE" => "%$xkeywordx%",
            "OR:Tblbook.company:LIKE" => "%$xkeywordx%",
        //array(
            "OR:Tbooks.content:LIKE" => "%$xkeywordx%",
            //"OR:Tbooks.long_indication:LIKE" => "%$xkeywordx%",
       // ),
        ),
        ));
        $query->groupBy('brand_name');
        $query->limit('50');
        $products = $modx->getCollection('Tblbook',$query);

81 Posts

Dunnock Reply #4, 13 years, 2 months ago

It might be bit more convenient to have fulltext index for all three columns "brand_name, company, content" and possibly for "long_indication"

You do it swiftly with phpmyadmin (I know you have it)

CREATE FULLTEXT INDEX 'tblbooks_search" ON tblbooks (brand_name, company, content, long_indication);

Then you need to alter your query a bit

$search = $modx->quote($xkeywordx, PDO::PARAM_STR);
$query = $modx->newQuery('Tblbook');
        $query->select(array (
            'Tblbook.system_product_id',
            'Tblbook.system_brand_id',
            'Tblbook.brand_name',
            'Tbooks.content',
            'Tblbook.company',
            'Tblbook.form_class',
            'Tgen.system_generic_id',
            'Tsec1.section1_id',
            'Tsec2.section2_id',
            'Tsec3.section3_id',
            'Tsec4.section4_id',
            "MATCH(Tblbook.brand_name, Tblbook.company, Tblbook.content, Tblbook.long_indication) AGAINST ($search)"
        ));
     
        $query->innerJoin('Tblbooks', 'Tbooks', array("Tblbook.system_brand_id = Tbooks.system_brands_id")); 
        $query->innerJoin('Tblbrandgenericgroupsms','Tgen',array("Tblbook.system_brand_id = Tgen.system_brand_id"));
        $query->innerJoin('Tblproductsections', 'Tprodsec', array(" Tblbook.system_product_id = Tprodsec.system_product_id")); 
        $query->leftJoin('Tblsection', 'Tsec', array(" Tprodsec.system_section_id = Tsec.system_section_id")); 
        $query->leftJoin('Tblsection1', 'Tsec1', array(" Tsec.system_section1_id = Tsec1.system_section1_id"));
        $query->leftJoin('Tblsection2', 'Tsec2', array(" Tsec.system_section2_id = Tsec2.system_section2_id"));
        $query->leftJoin('Tblsection3', 'Tsec3', array(" Tsec.system_section3_id = Tsec3.system_section3_id"));
        $query->leftJoin('Tblsection4', 'Tsec4', array(" Tsec.system_section4_id = Tsec4.system_section4_id"));
        $query->where(array("MATCH(Tblbook.brand_name, Tblbook.company, Tblbook.content, Tblbook.long_indication) AGAINST ($search)"
        ));
        $query->groupBy('brand_name');
        $query->limit('50');
        $products = $modx->getCollection('Tblbook',$query);

It uses fulltext index then, full text index does not perform always optimal with GROUP BY or ORDER BY but might help in your case.4

[Edit] Above works only for MyISAM engine table or if you have MySQL 5.6 then it works with InnoDB engine also

126 Posts

johnrobert27 Reply #5, 13 years, 2 months ago

thanks for the pointers but when I try this

CREATE FULLTEXT INDEX 'tblbooks_search" ON tblbooks (brand_name, company, content, long_indication);

MySQL gives an error

[Err] 1072 - Key column 'brand_name' doesn't exist in table

what would it be?

81 Posts

Dunnock Reply #6, 13 years, 2 months ago

Ummmm... odd? as you have it in your query. Check columns in the table and use those columns you want to use in the MATCH() part and in the index creation. To use the fulltext index, you always have to have all of the columns from that index in the MATCH() part.

You can have multiple fulltext indexes per table and different field combinations. But it can cause different kinds of issues. Just updating one page requires quite lot of work in the index, don't have the maths here now

also the indexes can be quite biggie then.

126 Posts

johnrobert27 Reply #7, 13 years, 2 months ago

the 'LIKE' method look for 2 fields in two different table do you think I have to create something like this to make it work?

CREATE FULLTEXT INDEX tblbooks_search ON tblbooks (content, long_indication);

CREATE FULLTEXT INDEX tblbooks_search ON tblbook (brand_name, company);

81 Posts

Dunnock Reply #8, 13 years, 2 months ago

Ah, did not notice those subtle differences. Yes, index cannot span over two tables. So you need to create it on both, then you need to duplicate the MATCH AGAINST codes in both select and where to match those indexed columns.

126 Posts

johnrobert27 Reply #9, 13 years, 2 months ago

like this?

$search = $modx->quote($xkeywordx, PDO::PARAM_STR);
$query = $modx->newQuery('Tblbook');
        $query->select(array (
            'Tblbook.system_product_id',
            'Tblbook.system_brand_id',
            'Tblbook.brand_name',
            'Tbooks.content',
            'Tblbook.company',
            'Tblbook.form_class',
            'Tgen.system_generic_id',
            'Tsec1.section1_id',
            'Tsec2.section2_id',
            'Tsec3.section3_id',
            'Tsec4.section4_id',
            "MATCH(Tblbook.brand_name, Tblbook.company)AGAINST ($search)",
            "MATCH(Tbooks.content, Tbooks.long_indication) AGAINST ($search)"
        ));
      
        $query->innerJoin('Tblbooks', 'Tbooks', array("Tblbook.system_brand_id = Tbooks.system_brands_id")); 
        $query->innerJoin('Tblbrandgenericgroupsms','Tgen',array("Tblbook.system_brand_id = Tgen.system_brand_id"));
        $query->innerJoin('Tblproductsections', 'Tprodsec', array(" Tblbook.system_product_id = Tprodsec.system_product_id")); 
        $query->leftJoin('Tblsection', 'Tsec', array(" Tprodsec.system_section_id = Tsec.system_section_id")); 
        $query->leftJoin('Tblsection1', 'Tsec1', array(" Tsec.system_section1_id = Tsec1.system_section1_id"));
        $query->leftJoin('Tblsection2', 'Tsec2', array(" Tsec.system_section2_id = Tsec2.system_section2_id"));
        $query->leftJoin('Tblsection3', 'Tsec3', array(" Tsec.system_section3_id = Tsec3.system_section3_id"));
        $query->leftJoin('Tblsection4', 'Tsec4', array(" Tsec.system_section4_id = Tsec4.system_section4_id"));
        $query->where(array("MATCH(Tblbook.brand_name, Tblbook.company)AGAINST ($search),
            MATCH(Tbooks.content, Tbooks.long_indication) AGAINST ($search)"
        ));
        $query->groupBy('brand_name');
        $query->limit('50');
        $products = $modx->getCollection('Tblbook',$query);

81 Posts

Dunnock Reply #10, 13 years, 2 months ago

Looks good