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%' ";
}