php - Efficiency in SQL query -
i have created search functionality cake application. built multiple select boxes in can select data, cycles through selected options , implements them sql syntax.
basically how function looks like:
$selectedfilters = $this->data; $selectsql = 'select agencies.agency, agencies.website_url, agencies.status, agencies.size, agencies.id, officedata.id, contactdata.name, contactdata.surname, contactdata.job_title, contactdata.email, contactdata.mobile, officecountrydata.country agencies left join (select agencies_industries.agency_id, agencies_industries.industry_id agencies_industries) industrydata on agencies.id = industrydata.agency_id left join (select agencies_professions.agency_id, agencies_professions.profession_id agencies_professions) professiondata on agencies.id = professiondata.agency_id left join (select agencies_sectors.agency_id, agencies_sectors.sector_id agencies_sectors) sectordata on agencies.id = sectordata.agency_id left join (select agencies_seniorities.agency_id, agencies_seniorities.seniority_id agencies_seniorities) senioritydata on agencies.id = senioritydata.agency_id left join (select agencies_zones.agency_id, agencies_zones.zone_id agencies_zones) zonesdata on agencies.id = zonesdata.agency_id left join (select agencies_countries.agency_id, agencies_countries.country_id agencies_countries) countrydata on agencies.id = countrydata.agency_id left join (select agencies_regions.agency_id, agencies_regions.region_id agencies_regions) regiondata on agencies.id = regiondata.agency_id left join (select agencies_cities.agency_id, agencies_cities.city_id agencies_cities) citydata on agencies.id = citydata.agency_id left join (select agencies_specialisms.agency_id, agencies_specialisms.specialism_id agencies_specialisms) specialismdata on agencies.id = specialismdata.agency_id left join (select offices.id, offices.agency_id, offices.hq offices offices.hq = "1") officedata on agencies.id = officedata.agency_id left join (select countries.id, countries.country countries) officecountrydata on officedata.hq = officecountrydata.id left join (select contacts.name, contacts.surname, contacts.agency_id, contacts.job_title, contacts.email, contacts.mobile contacts) contactdata on agencies.id = contactdata.agency_id '; $wheresql = ' 1 = 1 '; foreach($selectedfilters $key) foreach($key $name=>$value){ if(is_array($key)) foreach($key $key=>$value){ $i = 0; $connector = 'and'; if(is_array($value)){ foreach($value $value){ if($i > 0) $connector = 'or'; $i++; switch($key){ case 'profession': $wheresql .= $connector.' professiondata.profession_id = ' . $value . ' '; break; case 'specialism': $wheresql .= $connector.' specialismdata.specialism_id = ' . $value . ' '; break; case 'subspecialism': $wheresql .= ''; //$wheresql .= $connector.' subdata.sub_specialism_id = ' . $value . ' '; break; case 'seniority': $wheresql .= $connector.' senioritydata.seniority_id = ' . $value . ' '; break; case 'industry': $wheresql .= $connector.' industrydata.industry_id = ' . $value . ' '; break; case 'zone': $wheresql .= $connector.' zonesdata.zone_id = ' . $value . ' '; break; case 'country': $wheresql .= $connector.' countrydata.country_id = ' . $value . ' '; break; case 'region': $wheresql .= $connector.' regiondata.region_id = ' . $value . ' '; break; case 'city': $wheresql .= $connector.' citydata.city_id = ' . $value . ' '; break; case 'sector': $wheresql .= $connector.' sectordata.sector_id = ' . $value . ' '; break; case 'status': $wheresql .= $connector.' agencies.status = "' . $value . '" '; break; case 'size': $wheresql .= $connector.' agencies.size = "' . $value . '" '; break; } } } else if(!isblank($value) && $key != 'search') $wheresql .= $connector.' agencies.'.$key.' = "'.$value.'" '; } } $groupbysql = 'group agencies.id order agencies.id asc'; $resultagencies = $this->agency->query($selectsql . $wheresql . $groupbysql); $this->set(compact('resultagencies')); the problem encountered search works slow. happens because of using many left join commands. every left join selects data distinct tables , gathers them creating table. data displayed.
i need give me hint how not using many left joins.
cheers.
try one:
$selectsql = 'select agencies.agency, agencies.website_url, agencies.status, agencies.size, agencies.id, officedata.id, contactdata.name, contactdata.surname, contactdata.job_title, contactdata.email, contactdata.mobile, officecountrydata.country agencies left join agencies_industries industrydata on agencies.id = industrydata.agency_id left join agencies_professions professiondata on agencies.id = professiondata.agency_id left join agencies_sectors sectordata on agencies.id = sectordata.agency_id left join agencies_seniorities senioritydata on agencies.id = senioritydata.agency_id left join agencies_zones zonesdata on agencies.id = zonesdata.agency_id left join agencies_countries countrydata on agencies.id = countrydata.agency_id left join agencies_regions regiondata on agencies.id = regiondata.agency_id left join agencies_cities citydata on agencies.id = citydata.agency_id left join agencies_specialism specialismdata on agencies.id = specialismdata.agency_id left join offices officedata on (agencies.id = officedata.agency_id , officedata.hq = "1") left join countries officecountrydata on officedata.hq = officecountrydata.id left join contacts contactdata on agencies.id = contactdata.agency_id '; but might slow since join many tables. it's hard tell without knowing data , amount of rows you'll return. might want move joins subquery (like country) if return few rows. or can add information in seperate query.
edit: without knowing data , db-structure it's hard tell. there lot of things influence speed of query. first rewrite query tables not used selection (i.e. where) or fields want show not used in query. if make no selection (emtpy $selectedfilters) don't have include industries, professions, sectors, seniorities, etc tables.:
$selectedfilters = $this->data; $selectsql = 'select agencies.agency, agencies.website_url, agencies.status, agencies.size, agencies.id, officedata.id, contactdata.name, contactdata.surname, contactdata.job_title, contactdata.email, contactdata.mobile, officecountrydata.country agencies'; $sql2=' left join offices officedata on (agencies.id = officedata.agency_id , officedata.hq = "1") left join countries officecountrydata on officedata.hq = officecountrydata.id left join contacts contactdata on agencies.id = contactdata.agency_id '; $wheresql = ' 1 = 1 '; foreach($selectedfilters $key) foreach($key $name=>$value){ if(is_array($key)) foreach($key $key=>$value){ $i = 0; $connector = 'and'; if(is_array($value)){ foreach($value $value){ if($i > 0) $connector = 'or'; $i++; switch($key){ case 'profession': $wheresql .= $connector.' professiondata.profession_id = ' . $value . ' '; $sql2.=' left join agencies_professions professiondata on agencies.id = professiondata.agency_id '; break; case 'specialism': $wheresql .= $connector.' specialismdata.specialism_id = ' . $value . ' '; $sql2.=' left join agencies_specialism specialismdata on agencies.id = specialismdata.agency_id '; break; case 'subspecialism': $wheresql .= ''; //$wheresql .= $connector.' subdata.sub_specialism_id = ' . $value . ' '; break; case 'seniority': $wheresql .= $connector.' senioritydata.seniority_id = ' . $value . ' '; $sql2.=' left join agencies_seniorities senioritydata on agencies.id = senioritydata.agency_id '; break; case 'industry': $wheresql .= $connector.' industrydata.industry_id = ' . $value . ' '; $sql2=' left join agencies_industries industrydata on agencies.id = industrydata.agency_id '; break; case 'zone': $wheresql .= $connector.' zonesdata.zone_id = ' . $value . ' '; $sql2.=' left join agencies_zones zonesdata on agencies.id = zonesdata.agency_id '; break; case 'country': $wheresql .= $connector.' countrydata.country_id = ' . $value . ' '; $sql2.=' left join agencies_countries countrydata on agencies.id = countrydata.agency_id '; break; case 'region': $wheresql .= $connector.' regiondata.region_id = ' . $value . ' '; $sql2.=' left join agencies_regions regiondata on agencies.id = regiondata.agency_id '; break; case 'city': $wheresql .= $connector.' citydata.city_id = ' . $value . ' '; $sql2.=' left join agencies_cities citydata on agencies.id = citydata.agency_id '; break; case 'sector': $wheresql .= $connector.' sectordata.sector_id = ' . $value . ' '; $sql2.='left join agencies_sectors sectordata on agencies.id = sectordata.agency_id '; break; case 'status': $wheresql .= $connector.' agencies.status = "' . $value . '" '; break; case 'size': $wheresql .= $connector.' agencies.size = "' . $value . '" '; break; } } } else if(!isblank($value) && $key != 'search') $wheresql .= $connector.' agencies.'.$key.' = "'.$value.'" '; } } $groupbysql = 'group agencies.id order agencies.id asc'; $resultagencies = $this->agency->query($selectsql . $sql2 . $wheresql . $groupbysql); $this->set(compact('resultagencies')); second take @ indexes each table. make sure have index on fields use in joins.
third, @ field types use. don't use int if smallint large enough.
finaly: normalization great, it's better combine things, if means have duplicate data.
Comments
Post a Comment