0

This is my query and I need to return only 12 rows, but it takes 2 seconds with parameter LIMIT 12 while it takes only <0.2 to process all ~600 results (without LIMIT 12 or with LIMIT 1200 for example).

Server version: 5.5.38-0+wheezy1 (Debian)

My question is, how could I improve this and why is this behaving this way?

SELECT uploaded.*, floor((upvotes * 100) / (upvotes + downvotes)) AS percentage_liked
FROM video_views 
JOIN uploaded ON video_views.file_name = uploaded.file_name 
WHERE conversion_finished != 0 AND active = 1 
GROUP BY file_name 
ORDER BY COUNT(*) DESC 
LIMIT 12 <--------- PROBLEM

Explain LIMIT 12

+----+-------------+-------------+-------+--------------------------+---------------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table       | type  | possible_keys            | key           | key_len | ref                      | rows | Extra                                        |
+----+-------------+-------------+-------+--------------------------+---------------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | uploaded    | range | idx_file_name,idx_active | idx_active    | 4       | NULL                     |  545 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | video_views | ref   | idx_file_name            | idx_file_name | 14      | mydb.uploaded.file_name |  160 | Using index                                  |
+----+-------------+-------------+-------+--------------------------+---------------+---------+--------------------------+------+----------------------------------------------+

Explain LIMIT 1200 (Why does it use now type index instead of range?)

+----+-------------+-------------+-------+--------------------------+---------------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table       | type  | possible_keys            | key           | key_len | ref                      | rows | Extra                                        |
+----+-------------+-------------+-------+--------------------------+---------------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | uploaded    | index | idx_file_name,idx_active | idx_file_name | 14      | NULL                     |  600 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | video_views | ref   | idx_file_name            | idx_file_name | 14      | mydb.uploaded.file_name |  160 | Using index                                  |
+----+-------------+-------------+-------+--------------------------+---------------+---------+--------------------------+------+----------------------------------------------+

tbl uploaded

+--------------------------+--------------+------+-----+---------+----------------+
| Field                    | Type         | Null | Key | Default | Extra          |
+--------------------------+--------------+------+-----+---------+----------------+
| id                       | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id                  | int(11)      | NO   | MUL | NULL    |                |
| file_name                | varchar(4)   | NO   | MUL | NULL    |                |
| file_ext                 | varchar(255) | NO   |     | NULL    |                |
| file_title               | varchar(255) | NO   | MUL | NULL    |                |
| file_description         | text         | NO   |     | NULL    |                |
| file_tags                | varchar(255) | NO   | MUL | NULL    |                |
| upload_datetime          | datetime     | NO   |     | NULL    |                |
| file_size                | bigint(20)   | NO   |     | NULL    |                |
| file_name_keyword        | varchar(255) | NO   |     | NULL    |                |
| active                   | int(1)       | NO   | MUL | NULL    |                |
| publish_after_conversion | int(1)       | NO   |     | 0       |                |
| thumbnails_created       | int(1)       | NO   |     | NULL    |                |
| original_width           | int(11)      | NO   |     | NULL    |                |
| original_height          | int(11)      | NO   |     | NULL    |                |
| conversion_started       | datetime     | NO   | MUL | NULL    |                |
| conversion_finished      | datetime     | NO   |     | NULL    |                |
| quality                  | int(1)       | NO   |     | NULL    |                |
| converted_width          | int(11)      | NO   |     | NULL    |                |
| converted_height         | int(11)      | NO   |     | NULL    |                |
| video_duration           | int(11)      | NO   |     | NULL    |                |
| thumb_id                 | int(1)       | NO   |     | 0       |                |
| views                    | int(11)      | NO   |     | NULL    |                |
| upvotes                  | int(11)      | NO   |     | NULL    |                |
| downvotes                | int(11)      | NO   |     | NULL    |                |
+--------------------------+--------------+------+-----+---------+----------------+

tbl view_views

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| file_name | varchar(4)  | NO   | MUL | NULL    |                |
| ip_addr   | varchar(40) | NO   | MUL | NULL    |                |
| datetime  | datetime    | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5
  • Can you try the EXPLAIN with "LIMIT 12", but without "DESC" please. i.e. ORDER BY COUNT(*) LIMIT 12 Nov 17, 2014 at 2:15
  • It's almost the same as with ORDER BY COUNT(*) DESC LIMIT 12. Only in key idx_file_name is 67 rows instead of 160. But still it takes 2 seconds. Nov 17, 2014 at 10:38
  • If you have a solution it would be valuable to have this documented as an answer. It's OK to answer your own question and mark it as the accepted solution. Can you do this, please? Nov 19, 2014 at 1:09
  • I agree with @MichaelGreen. You should submit your rewritten query an an answer. I am sure it will be upvoted. Nov 19, 2014 at 3:35
  • Sorry, haven't been a while on Stack and totally forgot how to close the topic :] Nov 19, 2014 at 20:11

1 Answer 1

0

Rewrote query to the following, reduced query time to 0.016s:

SELECT *, floor((upvotes * 100) / (upvotes + downvotes)) AS percentage_liked
FROM (
   SELECT COUNT(*) AS views, file_name 
   FROM video_views 
   GROUP BY file_name 
   ORDER BY views DESC 
   LIMIT 12
 ) p JOIN uploaded ON p.file_name = uploaded.file_name WHERE conversion_finished != 0 AND active = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.