This Mysql SQL Select query takes 33 mins to execute

Question

Title says it all. Do you think anyone could help me untangle this? or if someone could point me out to what else could be causing it to take so much time. The query takes about half an hour to run. The guy who wrote this tried doing it in a loop, by removing the table from the last join statement and then querying the field.title for each vote. i was hoping to bring the result to about 5 mins.

some extra info:

The query result is 83,531 rows

The vote table size is 30 MB (261,169 rows)

SELECT `vote`.`id` `vote_id`, `branch`.`name` `branch`, `brand`.`name` `brand`, DATE(vote.created_at) `date`, HOUR(vote.created_at) `time_hour`,
            MINUTE(vote.created_at) `time_minute`, `vote`.`is_like`, `voter`.`name`, `voter`.`telephone`, `voter`.`email`, popups_votes.title  `popup_title`,
            popups_votes.value  `popup_value`, GROUP_CONCAT(dis.field SEPARATOR '|') `reasons`
            FROM (`vote`)
            LEFT JOIN `voter` ON `voter`.`id` = `vote`.`voter_id`
            LEFT JOIN `device` ON `device`.`id` = `vote`.`device_id`
            LEFT JOIN `branch` ON `branch`.`id` = `device`.`branch_id`
            LEFT JOIN `brand` ON `brand`.`id` = `branch`.`brand_id`
            LEFT JOIN `popups_votes` ON popups_votes.vote_id = vote.id
            LEFT JOIN (SELECT vote_dislike.vote_id `vote_id`, field.title `field` FROM vote_dislike
                    LEFT JOIN branch_dislike_field ON branch_dislike_field.id = vote_dislike.branch_dislike_id
                    LEFT JOIN field ON field.id = branch_dislike_field.field_id) dis
                    ON dis.vote_id = vote.id
            WHERE (vote.device_id in
             (
             Select d.id
             From device d
             WHERE d.branch_id IN (SELECT id FROM branch WHERE brand_id = 7)
             )
             )
             AND (vote.created_at >= FROM_UNIXTIME('$from_time') AND vote.created_at <= FROM_UNIXTIME('$to_time') )
            GROUP BY vote.id

EDIT: this is the explain {query} output:

+------+-------------+----------------------+--------+----------------------+-----------+---------+-------------------------------------------+------+----------------------------------------------+
| id   | select_type | table                | type   | possible_keys        | key       | key_len | ref                                       | rows | Extra                                        |
+------+-------------+----------------------+--------+----------------------+-----------+---------+-------------------------------------------+------+----------------------------------------------+
|    1 | PRIMARY     | branch               | ref    | PRIMARY,brand_id     | brand_id  | 4       | const                                     |   20 | Using index; Using temporary; Using filesort |
|    1 | PRIMARY     | d                    | ref    | PRIMARY,branch_id    | branch_id | 4       | river_back.branch.id                      |    1 | Using index                                  |
|    1 | PRIMARY     | vote                 | ref    | device_id,created_at | device_id | 4       | river_back.d.id                           | 1200 | Using where                                  |
|    1 | PRIMARY     | voter                | eq_ref | PRIMARY              | PRIMARY   | 4       | river_back.vote.voter_id                  |    1 |                                              |
|    1 | PRIMARY     | device               | eq_ref | PRIMARY              | PRIMARY   | 4       | river_back.d.id                           |    1 |                                              |
|    1 | PRIMARY     | branch               | eq_ref | PRIMARY              | PRIMARY   | 4       | river_back.device.branch_id               |    1 | Using where                                  |
|    1 | PRIMARY     | brand                | eq_ref | PRIMARY              | PRIMARY   | 4       | river_back.branch.brand_id                |    1 | Using where                                  |
|    1 | PRIMARY     | popups_votes         | ref    | vote_id              | vote_id   | 5       | river_back.vote.id                        |  602 |                                              |
|    1 | PRIMARY     | vote_dislike         | ref    | vote_id              | vote_id   | 4       | river_back.vote.id                        |    1 |                                              |
|    1 | PRIMARY     | branch_dislike_field | eq_ref | PRIMARY              | PRIMARY   | 4       | river_back.vote_dislike.branch_dislike_id |    1 | Using where                                  |
|    1 | PRIMARY     | field                | eq_ref | PRIMARY              | PRIMARY   | 4       | river_back.branch_dislike_field.field_id  |    1 | Using where                                  |
+------+-------------+----------------------+--------+----------------------+-----------+---------+-------------------------------------------+------+----------------------------------------------+

That was my first thought, i indexed the vote.created_at column since the where clause depends on time. — PIRATE FIFI, Jul 20 '17 at 22:43
I would try experimenting with a few more columns and see what kind of result you get. ie, voter_id, device_id, branch_id — Kevin P, Jul 20 '17 at 22:45

score 2 · Accepted Answer · answered Jul 20 '17 at 22:46

You should check that all the data you are selecting are indexed and you have foreign keys.

How do MySQL indexes work?

Basically an index on a table works like an index in a book (that's where the name came from):

Let's say you have a book about databases and you want to find some information about, say, storage. Without an index (assuming no other aid, such as a table of contents) you'd have to go through the pages one by one, until you found the topic (that's a full table scan). On the other hand, an index has a list of keywords, so you'd consult the index and see that storage is mentioned on pages 113-120,231 and 354. Then you could flip to those pages directly, without searching (that's a search with an index, somewhat faster).

Basics of Foreign Keys in MySQL?

FOREIGN KEYS just ensure your data are consistent.

They do not improve queries in sense of efficiency, they just make some wrong queries fail.

Oh! i thought foreign keys are automatically indexes! I ended up fixing it by indexing popups_votes.vote_id. now it runs in seconds. — PIRATE FIFI, Jul 20 '17 at 23:41
@PIRATEFIFI glad to help :) but seconds are still a lot. You should keep improving your queries — Niv Apo, Jul 20 '17 at 23:42

score 0 · Answer 2 · answered Jul 21 '17 at 02:07

Do not use LEFT unless you are expecting the "right" table to have missing rows.
In particular, the Optimizer probably cannot start with the 'derived table' since it is hiding to the right of a LEFT.
Do not use IN ( SELECT ... ); if possible change to EXISTS ( SELECT * ...) or JOIN.
Try to avoid the "inflate-deflate" caused by JOIN ... GROUP BY. If possible find the ids of interest without needing a GROUP BY, then JOIN to the other tables.

Putting many of those together, does this get you close to the desired result, at least in the sense of getting the correct vote.id values?

SELECT vote.id
    FROM vote AS v
    JOIN (
        SELECT  vote_dislike.vote_id `vote_id`, field.title `field`
            FROM  vote_dislike AS vd
            LEFT JOIN  branch_dislike_field AS bd
              ON bd.id = vd.branch_dislike_id
            LEFT JOIN  field
              ON field.id = bd.field_id
          ) AS dis  ON dis.vote_id = v.id
    JOIN device AS d ON v.device_id = d.id
    JOIN branch AS b ON d.branch_id = b.id
    WHERE b.brand_id = 7
      AND v.created_at >= ...
      AND v.created_at <= ...

Then:

SELECT lots of stuff
    FROM ( the above query ) AS x
    JOIN vote v  ON x.id = v.id   -- yes, dig back into `vote` for the other stuff
    JOIN voter ...
    JOIN ...

but with no GROUP BY.

This Mysql SQL Select query takes 33 mins to execute

2 Answers2