Optimize a query with expression

I have a query that runs about 1.40 seconds on average.

EXPLAIN SELECT (UNIX_TIMESTAMP() - votes.time) as time, `votes`.`time` as `timestamp`, `users`.`avatar`, `location`.`country_code`, `users`.`full_name`, `contests`.`contest_id`, `contestants`.`contestant_name`, `contestants`.`extSrcVideo`, `contestants`.`contestant_id`, `contests`.`contest_cxid`, `contestants`.`contestant_img`, `contestants`.`youtubeVideoId`, `contests`.`contest_slug`, `contests`.`contest_name`, `votes`.`vote_number` as `qty`
FROM `contest_assoc`
JOIN `contestants` ON `contestants`.`contestant_id` = `contest_assoc`.`contestant_id`
JOIN `contests` ON `contests`.`contest_id` = `contest_assoc`.`contest_id`
JOIN `votes` ON `votes`.`assoc_id` = `contest_assoc`.`contest_assoc_id`
JOIN `users` ON `users`.`ID` = `votes`.`user_id`
JOIN `location` ON `location`.`user_id` = `users`.`ID`
GROUP BY `votes`.`user_id`
ORDER BY (UNIX_TIMESTAMP() - votes.time) ASC
LIMIT 10;

+----+-------------+---------------+------------+--------+---------------+---------+---------+-----------------------------------------+--------+----------+--------------------------------------------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref                                     | rows   | filtered | Extra                                      |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-----------------------------------------+--------+----------+--------------------------------------------+
|  1 | SIMPLE      | votes         | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                                    | 106312 |   100.00 | Using temporary; Using filesort            |
|  1 | SIMPLE      | contest_assoc | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | onlyWinners.votes.assoc_id              |      1 |   100.00 | Using where                                |
|  1 | SIMPLE      | contests      | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | onlyWinners.contest_assoc.contest_id    |      1 |   100.00 | NULL                                       |
|  1 | SIMPLE      | contestants   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | onlyWinners.contest_assoc.contestant_id |      1 |   100.00 | NULL                                       |
|  1 | SIMPLE      | users         | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | onlyWinners.votes.user_id               |      1 |   100.00 | NULL                                       |
|  1 | SIMPLE      | location      | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                                    | 135670 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-----------------------------------------+--------+----------+--------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

if I add index to location.user, the filtered goes up from 10 to 100, but the executetion time goes up to 1.85 seconds.

I've tried to apply some indexing, but I failed, probably I've missed something here.

I think the challenge lies in the expression (UNIX_TIMESTAMP() - votes.time), but I might be wrong.

I want to make it 3-4 times faster, is this possible?

Solution

Your query has no WHERE filters, but only an ORDER BY ... LIMIT filter. And it has a puzzling GROUP BY clause that isn't matched by the SELECT clause.

So your best path to a fast query plan is to use an index to access the votes table.

Get rid of the GROUP BY clause.
Change the ORDER BY clause to ORDER BY votes.time DESC. This will give you the same result as your sample query, but uses a direct column reference rather than an expression reference.
Put this index on your votes table.
```
CREATE INDEX time ON votes(time DESC);
```

This will let that index be used to find the ten most recent rows in that table, which should cut down on the server workload.