How to optimize this MySQL query/structure?

Question

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.

If you would, please show the EXPLAIN PLAN output in your question. — Tripp Kinetics
– Tripp Kinetics, Commented Jun 17, 2014 at 18:53
The picture is attached, it's not Oracle, it's MySQL. Instead of the execution plan, explain statement is used here. — ZurabWeb
– ZurabWeb, Commented Jun 17, 2014 at 18:58
LIKE "%test%" and your dependent picture_name subquery will likely slow you down. — Joachim Isaksson
– Joachim Isaksson, Commented Jun 17, 2014 at 19:02
Are you using InnoDB? And is there already an index on rn.name? Cause if so, I bet AND rn.name LIKE "%test%" is the real killer here. MySQL can't leverage an index on a LIKE statement like that. More info on that here: stackoverflow.com/questions/10354248/…. Also, double check that your join conditions are uniquely identifying rows in the other tables. The explain output on tables l, p, and pictures might indicate your key is not specific enough (it might not though; it's not conclusive from the output of that explain statement) — Evan Volgas
– Evan Volgas, Commented Jun 17, 2014 at 19:04
Since you require rn.name LIKE "%test%", remove rn.name != "". — Marcus Adams
– Marcus Adams, Commented Jun 17, 2014 at 19:10

Joachim Isaksson · Accepted Answer · 2014-06-18 15:21:48Z

1

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

answered Jun 18, 2014 at 15:21

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ZurabWeb Over a year ago

The solution is pretty good, but it didn't improve performance a lot - 43+ seconds. Thanks.

ZurabWeb Over a year ago

my original test was wrong. The query time got reduced to 6+ seconds, thank you very much for help, Joachim!

Collectives™ on Stack Overflow

How to optimize this MySQL query/structure?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related