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                                  |
+------+-------------+----------------------+--------+----------------------+-----------+---------+-------------------------------------------+------+----------------------------------------------+

Answer 1

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.