CODEWITHSUNDEEP
phpmysql
Nicolas Racine
Nicolas Racine

Reputation: 1061

Optimize a all related field search method

Id like to see if there is any better way that i could optimized the following search method.

I am completely aware that i am currently returning some un-used field from my mysql query. I plan on actually using them. So I don't "double fetch" data once I am using the Customer constructor

        static function ByAny($SearchString){
            $Get = dbCon::$dbCon -> prepare("
SELECT cC.Id as cC_Id,cC.FirstName as cC_FirstName,cC.LastName as cC_LastName,cC.EmailAddress as cC_EmailAddress,cC.Note as cC_Note,
    cF.Id as cF_Id,cF.Name as cF_Name, cF.Notes as cF_Note,
    cA.Address as cA_Address, cA.City as cA_City, cA.PostalCode as cA_PostalCode,cA.Province as cA_Province, cA.Country as cA_Country,cA.Description as cA_Description,
    cFA.Address as cFA_Address, cFA.City as cFA_City, cFA.PostalCode as cFA_PostalCode,cFA.Province as cFA_Province, cFA.Country as cFA_Country,cFA.Description as cFA_Description
FROM
    `customer_customers` as cC
        LEFT JOIN customer_farms as cF ON (cC.FarmId = cF.Id)
        LEFT JOIN addresses as cA ON (cC.AddressId = cA.Id)
        LEFT JOIN addresses as cFA ON (cF.AddressId = cFA.Id)
WHERE
    CONCAT_WS(
        cC.FirstName,' ',cC.LastName,' ',cC.EmailAddress,' ',cC.Note,' ',
        cF.Name,' ',cF.Notes,' ',
        cA.Address,' ',cA.City,' ',cA.PostalCode,' ',cA.Province,' ',cA.Country,' ',cA.Description,
        cFA.Address,' ',cFA.City,' ',cFA.PostalCode,' ',cFA.Province,' ',cFA.Country,' ',cFA.Description
    ) LIKE :Like;
            ");

            $CustomerObjAr = array();

            $SearchResult = null;
            foreach(explode(' ',$SearchString) as $Q){ //Explode string by spaces, then search all words
                $Get -> bindValue(':Like','%'.$Q.'%');
                try{
                    $Get -> execute();

                    if(is_null($SearchResult)){ // Is first time search run, All result should be kept.
                        $SearchResult = array();
                        foreach($Get->fetchAll(\PDO::FETCH_ASSOC) as $cData){
                            $SearchResult[] = $cData['cC_Id'];
                        }
                    }else{ // We already have result, lets compare them with new result, and keep only matching ( Closing narrowing search result )
                        $cSearchResult = array();
                        foreach($Get->fetchAll(\PDO::FETCH_ASSOC) as $cData){
                            $cSearchResult[] = $cData['cC_Id'];
                        }
                        $SearchResult = array_intersect($SearchResult,$cSearchResult);
                    }
                    if(is_array($SearchResult) && count($SearchResult) == 0) //No more result, lets terminate this now.
                        return $SearchResult;

                }catch (\PDOException $e){
                    die("Error while searching customers by any field: ".$e->getMessage());
                }
            }
            foreach($SearchResult as $cId){
                $CustomerObjAr[] = new Customer($cId);
            }
            return $CustomerObjAr;
        }

I was thinking to add a IN (1,2,3) Clause of the already known matching result, But I am un-certain of the actual benefits of providing a list, Would it speedthings up on mysql side ? or will it slow it down by first selecting those and then runing CONCAT() LIKE?

Thanks for input and constructive criticism.

Upvotes: 0

Views: 29

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562651

The method you are doing, CONCAT() LIKE ? is bound to do a table-scan. So it will get slower in direct proportion to the size of your table.

If you need to do a fulltext search across multiple columns, and you want it to run efficiently, you should learn about MySQL's fulltext search functions.

I did a presentation comparing the old-school LIKE '%pattern%' type queries versus searches using fulltext indexes. It depends on how much data you have, but fulltext indexes can make your queries hundreds or thousands of times faster than doing searches like you're doing it.

See my presentation: Fulltext Search Throwdown.

Upvotes: 2

Related Questions