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, Jun 17 '14 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, Jun 17 '14 at 18:58
`LIKE "%test%"` and your dependent picture_name subquery will likely slow you down. — Joachim Isaksson, Jun 17 '14 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: http://stackoverflow.com/questions/10354248/optimizing-mysql-like-string-queries-in-innodb. 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, Jun 17 '14 at 19:04
Since you require `rn.name LIKE "%test%"`, remove `rn.name != ""`. — Marcus Adams, Jun 17 '14 at 19:10
l,p and pictures are utilizing indexes successfully (ref column), although not primary ones, but still good enough as they only need to search in up to 54 entries, that shouldn't be too bad. All joins use indexes, just doubled checked. Thanks for the suggestions. — ZurabWeb, Jun 17 '14 at 19:12
The subquery is very specific, it only needs to lookup and sort up to 4 entries. — ZurabWeb, Jun 17 '14 at 19:13
@Piero The dependent subquery may be simple, but it's still executed almost 4000 times. — Joachim Isaksson, Jun 18 '14 at 06:41
@Joachim Isaksson, any way to get rid of it? I tried converting it to JOIN, but as GROUP statement goes first - wrong data gets returned. Similar problem is discussed here: http://stackoverflow.com/questions/18762726/mysql-join-group-by-order-by/18763815#18763815 — ZurabWeb, Jun 18 '14 at 14:46
@Piero Added an answer with an (untested) join alternative. I'd add it as a comment since I don't know whether it'll make a positive difference, but it was a bit too long to add here :) — Joachim Isaksson, Jun 18 '14 at 15:23

score 1 · Accepted Answer · answered Jun 18 '14 at 15:21

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

The solution is pretty good, but it didn't improve performance a lot - 43+ seconds. Thanks. — ZurabWeb, Jun 18 '14 at 15:34
my original test was wrong. The query time got reduced to 6+ seconds, thank you very much for help, Joachim! — ZurabWeb, Jun 18 '14 at 15:45

How to optimize this MySQL query/structure?

1 Answers1