Can this MySQL join query be optimized?

Question

I have this query which takes 27 seconds to execute:

SELECT ocal_files.*, count(DISTINCT ocal_favs.username) as favs 
FROM ocal_files
   INNER JOIN ocal_favs on ocal_favs.clipart_id = ocal_files.id 
GROUP BY ocal_files.id 
ORDER BY favs DESC

(instead of username it should be user_id, because I have table for users)

ocal_files has 37457 rows and ocal_favs has 18263

EDIT result of explain

mysql> EXPLAIN SELECT ocal_files.*, count(DISTINCT ocal_favs.username) as favs FROM ocal_files INNER JOIN ocal_favs on ocal_favs.clipart_i
d = ocal_files.id GROUP BY ocal_files.id ORDER BY favs DESC;                                                                             
+----+-------------+------------+--------+----------------+---------+---------+---------------------------------+-------+---------------------------------+
| id | select_type | table      | type   | possible_keys  | key     | key_len | ref                             | rows  | Extra                           |
+----+-------------+------------+--------+----------------+---------+---------+---------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | ocal_favs  | ALL    | rlb_clipart_id | NULL    | NULL    | NULL                             | 18622 | Using temporary; Using filesort|
|  1 | SIMPLE      | ocal_files | eq_ref | PRIMARY        | PRIMARY | 4       | openclipart.ocal_favs.clipart_id |     1 | Using where                    |
+----+-------------+------------+--------+----------------+---------+---------+---------------------------------+-------+---------------------------------+
2 rows in set (0.00 sec)

Why is it slow? Can it be optimized? If yes then how?

What is the execution plan? What are the indexes on the tables involved? — user330315
– user330315, Commented Jul 2, 2012 at 11:37
Have you ANALYZEd your tables yet? Can you ask MySQL to EXPLAIN the query? — Simon Richter
– Simon Richter, Commented Jul 2, 2012 at 11:39
@SimonRichter I've added result of EXPLAIN, Didn't know about ANALYZE it's speed up to 3.5 secs — jcubic
– jcubic, Commented Jul 2, 2012 at 12:00

Peter Lang · Accepted Answer · 2012-07-02 11:59:16Z

2

Try to create an index on

ocal_favs ( clipart_id, username )

and make sure that there is a NOT NULL constraint on ocal_favs.username or add ocal_favs.username IS NOT NULL as a condition.

This should allow to get all information from ocal_files and that index.

answered Jul 2, 2012 at 11:59

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jcubic Over a year ago

I've never use indexes before do I need to call this query: CREATE INDEX favs_index ON ocal_favs (clipart_id, username)?

jcubic Over a year ago

Because I trust people and I heard that indexes increase performance.

Peter Lang Over a year ago

Indexes can certainly increase performance, and I assume that this one will help you, but don't trust anything that you can test instead :)

Bud Damyanov · Accepted Answer · 2012-07-02 11:59:36Z

1

A good approach when dealing with SQL optimization is to select ONLY fields you need, not all of them in your case. This almost always have a huge impact on performance, especially when fields are BLOB's. And, as other users pointed out - the indexing is also very important, BUT only if you have created it properly. A use of LIMIT clause is also good idea, if you do not have need to display your result at once (I`m in doubt that this is the case here, because I don't believe that you will display the result of 30000+ records to a user browser)...

answered Jul 2, 2012 at 11:59

Bud Damyanov

32.1k7 gold badges48 silver badges53 bronze badges

Collectives™ on Stack Overflow

Can this MySQL join query be optimized?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related