slow query when using order by clause and group by clause

Query taking 28 sec to execute.

select DISTINCT home_list.id as 'property_idlist',home_list.address as 'property_addr',home_list.city as 'property_city',home_list.county as 'property_county',home_list.state as 'property_state',home_list.zipcode as 'property_zipcode',franchise_lead.request_home_address as request_home_address,franchise_lead.id as franchise_lead_id,  CONCAT(COALESCE(home_list.address,''),' ',COALESCE(home_list.city,''),' ',COALESCE(home_list.state,''),' ',COALESCE(home_list.zipcode,'')) as 'address_lead' from `franchise_lead` left join `home_list` on `home_list`.`id` = `franchise_lead`.`pId` where (`home_list`.`id` != '' and `franchise_lead`.`pId` != 0 or `franchise_lead`.`request_home_address` != '') and `franchise_lead`.`isDeleted` = 0 and `franchise_lead`.`isarchived` = 0 group by `franchise_lead`.`request_home_address`, `property_idlist`, `franchise_lead`.`id` order by `home_list`.`address` asc limit 101 offset 0

Tray2

3 years ago

Level 73

I suggest firing up your sql editor of choice and run EXPLAIN on your query, and add the necessary indexes. I would also try removing the DISTINCT since it's usually not a good idea to use it unless you really have to.

Please or to participate in this conversation.