Reputation: 1024

Why mysql sql query is taking long time to get the result?

In mysql db table contact_details I have almost 12,000 rows and it's continuously updating.

Now I have a search form where I need to search data from the db table contact_details

For e.g : I am searching 2 in type column from contact_details table and there are almost 11,000 records of 2.

In this situation, my sql query is taking long time to produce result ! Sometime it's showing me Maximum time exceed. What should I do to get the result more quickly ?

Here is the contact_details table look like :

Here is the search form look like with error message :

I am using following sql query to get the search result :

if(!empty($ad_keyword)) {
    $getSearch = "SELECT * FROM (SELECT GROUP_CONCAT(DISTINCT keywordName ORDER BY keywordName) as keywordName, ";
}
else{
    $getSearch = "SELECT ";
}
$getSearch .= " cd.cdid, cd.family_name, cd.given_name, cd.department, cd.title, company.*, users.nickname, contact_label.label_data  FROM
            contact_details as cd
            LEFT JOIN users ON users.user_id = cd.user_id
            LEFT JOIN company ON company.cid = cd.cid
            LEFT JOIN contact_docs ON contact_docs.cdid = cd.cdid
            LEFT JOIN userkeywords ON userkeywords . cdid = cd . cdid
            LEFT JOIN keywords ON keywords . kid = userkeywords . kid
            LEFT JOIN contact_label ON contact_label.cdid = cd.cdid
            WHERE 1=1 ";

    if(!empty($ad_company)){
        $getSearch .= "AND company.company_name LIKE '$ad_company%' ";
    }
    if(!empty($ad_fname)){
        $getSearch .= "AND cd.family_name LIKE '$ad_fname%' ";
    }
    if(!empty($ad_department)){
        $getSearch .= "AND cd.department LIKE '$ad_department%' ";
    }

    if(!empty($ad_mp)){
        $getSearch .= "AND cd.mp >= '$ad_mp' "; 
    }
    if(!empty($ad_e2)){
        $getSearch .= "AND cd.e2 >= '$ad_e2' "; 
    }
    if(!empty($ad_pl)){
        $getSearch .= "AND cd.pl >= '$ad_pl' "; 
    }
    if(!empty($ad_ap)){
        $getSearch .= "AND cd.ap >= '$ad_ap' "; 
    }
    if(!empty($ad_j2)){
        $getSearch .= "AND cd.j2 >= '$ad_j2' "; 
    }

    if(!empty($ad_agreater)){
        $getSearch .= "AND cd.age >= '$ad_agreater' ";  
    }
    if(!empty($ad_aless)){
        $getSearch .= "AND cd.age <= '$ad_aless' "; 
    }
    if(!empty($ad_agreater) && !empty($ad_aless)){
        $getSearch .= "AND cd.age BETWEEN '$ad_agreater' AND '$ad_aless'";  
    }

    if(!empty($ad_sgreater)){
        $getSearch .= "AND cd.comp >= '$ad_sgreater' "; 
    }
    if(!empty($ad_sless)){
        $getSearch .= "AND cd.comp <= '$ad_sless' ";    
    }
    if(!empty($ad_sgreater) && !empty($ad_sless)){
        $getSearch .= "AND cd.comp BETWEEN '$ad_sgreater' AND '$ad_sless'"; 
    }


    if(!empty($ad_noteterm)){    
        $ad_noteterm = preg_replace("/\{ASUSIBBIR\}(.+?)\s:\s(.+?)\{ASUSIBBIR\}/m", "$2", $ad_noteterm);
        $getSearch .= "AND LOCATE('$ad_noteterm', REPLACE (notesUpdate, '{ASUSIBBIR}', ' '))";
    }

    if(!empty($ad_cnote)){    
        $getSearch .= "AND LOCATE('$ad_cnote', cd.characterNotes)"; 
    }
    if(!empty($ad_twork)){    
        $getSearch .= "AND contact_label.label_data LIKE '%$ad_twork%'";    
    }
    if(!empty($ad_tmobile)){    
        $getSearch .= "AND cd.mobile_phone like '%$ad_tmobile%'";   
    }

    if(!empty($ad_resume)){    
        $getSearch .= "AND LOCATE('$ad_resume', contact_docs.file_content)";    //is this the resume? yes
    }

    if(!empty($ad_datefrom) && empty($ad_dateto)){    
            $getSearch .= "AND cd.created_date BETWEEN '$ad_datefrom'AND '$date'";  
    }

    if(!empty($ad_dateto) && empty($ad_datefrom)){    
        $getSearch .= "AND cd.created_date BETWEEN date('0000-00-00') AND '$ad_dateto' "; 
    }

    if(!empty($ad_datefrom) && !empty($ad_dateto)){
        $getSearch .= "AND cd.created_date BETWEEN '$ad_datefrom' AND '$ad_dateto'";    
    }

    if(!empty($ad_type)){    
        $getSearch .= "AND cd.type = '$ad_type' ";  
    }

    if(!empty($ad_wemail)){
        $getSearch .= "AND cd.email LIKE '$ad_wemail%'";    
    }

    if(!empty($ad_pemail)){
        $getSearch .= "AND cd.email_private LIKE '$ad_pemail%'";    
    }

    if(!empty($ad_title)){
        $getSearch .= "AND cd.title LIKE '$ad_title%'"; 
    }

    if(!empty($ad_source)){
        $getSearch .= "AND cd.source LIKE '$ad_source%'";   
    }


    if(!empty($ad_consultant)){
        $getSearch .= "AND users.nickname LIKE '%$ad_consultant%'"; 
    }

    if(!empty($ad_keyword)){
        $ad_keyword_param = str_replace(",","','",$ad_keyword);
        $getSearch .= " AND keywords.keywordName IN ('$ad_keyword_param') ";
    }

    $getSearch .= " GROUP BY cd.user_id, cd.cid, cd.cdid ";

    if(!empty($ad_keyword)){
        $ad_keyword_param = str_replace(",",",",$ad_keyword);
        $getSearch .= " ) as a WHERE keywordName LIKE '$ad_keyword_param%' ";
    }

Upvotes: 0