CODEWITHSUNDEEP
mysqldatabaseperformanceoptimizationindexing
ZurabWeb
ZurabWeb

Reputation: 1368

How to optimize this MySQL query/structure?

I got this huge query here:

SELECT `l`.`web_id`, `rn`.`resort_id`, `rn`.`name`,
    `rn`.`address1`, `rn`.`city`, `rn`.`state`, `rn`.`postal_code`,
    `rn`.`country`, `p`.`picture_name`, `p`.`picture_url`,
    `ra`.`destination`, COUNT(`d`.`web_id`) AS `adsnum` 
FROM `resort_name` `rn` 
LEFT JOIN `location` `l` ON `rn`.`name`=`l`.`property_name` 
LEFT JOIN `pictures` `p` ON `p`.`resort_id`=`rn`.`resort_id`
    AND `p`.`picture_name` = (
        SELECT `picture_name` FROM `pictures`
        WHERE `resort_id`=`rn`.`resort_id`
        ORDER BY `priority` ASC
        LIMIT 1
    ) 
LEFT JOIN `addata` `d` ON `d`.`web_id`=`l`.`web_id` 
    AND `d`.`caption_header`="Sale"
    AND `d`.`price_desc` != "Sold"
    AND `d`.`frea`="1"
LEFT JOIN `resort_attributes` `ra` ON `ra`.`resort_id`=`rn`.`resort_id` 
WHERE `rn`.`name` != ""
    AND `rn`.`status`="Active"
    AND `rn`.`name` LIKE "%test%"
GROUP BY `rn`.`name` 
ORDER BY `rn`.`name` ASC 
LIMIT 0, 50

The query takes 80+ seconds to run. The explain statement results picture is attached: enter image description here

I believe that's that type ALL that kills the performance.

How do I index the tables properly so the performance improves?

Thanks.

Upvotes: 0

Views: 57

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

Untested, but you could try rewriting the dependent subquery as a join, although at this level of complexity it's hard to foresee if MySQL's optimizer does a better job with that;

SELECT `l`.`web_id`, `rn`.`resort_id`, `rn`.`name`,
    `rn`.`address1`, `rn`.`city`, `rn`.`state`, `rn`.`postal_code`,
    `rn`.`country`, `p`.`picture_name`, `p`.`picture_url`,
    `ra`.`destination`, COUNT(`d`.`web_id`) AS `adsnum` 
FROM `resort_name` `rn` 
LEFT JOIN `location` `l` 
  ON `rn`.`name`=`l`.`property_name` 
LEFT JOIN `pictures` `p` 
  ON `p`.`resort_id`=`rn`.`resort_id`
LEFT JOIN `pictures` `p2` 
  ON `p2`.`resort_id`=`rn`.`resort_id`
 AND `p`.priority > `p2`.`priority`
LEFT JOIN `addata` `d` 
  ON `d`.`web_id`=`l`.`web_id` 
 AND `d`.`caption_header`="Sale"
 AND `d`.`price_desc` != "Sold"
 AND `d`.`frea`="1"
LEFT JOIN `resort_attributes` `ra` 
  ON `ra`.`resort_id`=`rn`.`resort_id` 
WHERE `rn`.`status`='Active'
  AND `rn`.`name` LIKE '%test%'
  AND `p2`.`priority` IS NULL
GROUP BY `rn`.`name` 
ORDER BY `rn`.`name` ASC 
LIMIT 0, 50

Upvotes: 1

Related Questions