Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm trying to troubleshoot a high load problem on a server, Today for some reason MySQL is taking up excessive CPU time. I have enabled slow-queries and found this query and others similar.

The tables has around 700k rows.

SELECT SUM( likes ) AS likes, image_id FROM post_files_likes WHERE image_id NOT IN(563593,591800,578109,581180,515832,646310,670601,626185,689090,80019,399472,468198) AND date > DATE_SUB( '2013-08-19' , INTERVAL 1 MONTH ) GROUP BY image_id ORDER BY likes DESC LIMIT 12;

`

mysql> describe post_files_likes
    -> ;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(10) | NO   | PRI | NULL    | auto_increment |
| image_id | int(10) | NO   | MUL | NULL    |                |
| likes    | int(11) | NO   |     | NULL    |                |
| date     | date    | NO   |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT SUM( likes ) AS likes, image_id FROM post_files_likes WHERE image_id NOT IN(563593,591800,578109,581180,515832,646310,670601,626185,689090,80019,399472,468198) AND date > DATE_SUB( '2013-08-19' , INTERVAL 1 MONTH ) GROUP BY image_id ORDER BY likes DESC LIMIT 12;
+----+-------------+------------------+-------+---------------------+------------+---------+------+--------+----------------------------------------------+
| id | select_type | table            | type  | possible_keys       | key        | key_len | ref  | rows   | Extra                                        |
+----+-------------+------------------+-------+---------------------+------------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | post_files_likes | range | image_id,image_id_2 | image_id_2 | 4       | NULL | 709885 | Using where; Using temporary; Using filesort |
+----+-------------+------------------+-------+---------------------+------------+---------+------+--------+----------------------------------------------+
1 row in set (2.92 sec)

I have run this query a few times and get anywhere between 0 to 30 seconds.

Is there something fundamentally wrong with this query? or is this query taking a long time due to other issues with mysql?

EDIT

 SHOW INDEX FROM post_files_likes;
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| post_files_likes |          0 | PRIMARY    |            1 | id          | A         |      710969 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          0 | image_id   |            1 | image_id    | A         |      355484 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          0 | image_id   |            2 | date        | A         |      710969 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          1 | image_id_2 |            1 | image_id    | A         |      355484 |     NULL | NULL   |      | BTREE      |         |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.05 sec)

EDIT Added indexes

mysql> SHOW INDEX FROM post_files_likes;
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| post_files_likes |          0 | PRIMARY    |            1 | id          | A         |      711181 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          0 | image_id   |            1 | image_id    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          0 | image_id   |            2 | date        | A         |      711181 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          1 | image_id_2 |            1 | image_id    | A         |      237060 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          1 | likes      |            1 | likes       | A         |         445 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          1 | likes      |            2 | date        | A         |        4709 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          1 | likes      |            3 | image_id    | A         |      711181 |     NULL | NULL   |      | BTREE      |         |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
share|improve this question

3 Answers 3

up vote 2 down vote accepted

There's nothing instrinsically wrong with the query - but you forgot to tell us how the indexes are configured.

Optimally this query would use a BTREE index on post_files_likes.date, but there are cases where this won't be used by the DBMS / won't improve the performance (e.g. if the cardinality of the date column is low the DBMS won't use it, a hash based index is very innefficient for looking up ranges of data).

Adding image_id then LIKES to the index (AFTER date) means that the index is covering and the query can be satisfied without reference to the table data. But can a post be liked more than once at exactly the same time?

If you're running this wquery frequently then denormalizing and / or caching the results will help, as (again inferring from context) the data does not appear to be needed in real-time.

share|improve this answer
    
Hey thanks, I have added the output of SHOW INDEX FROM post_files_likes;` Do you think I should add an index to date? Or does this mean it already has an index? –  Shutupsquare Aug 19 '13 at 13:18
    
Yes you should an index, but not JUST date. You should have a BTREE index on (date, image_id [, likes]). And your primary key index on id should be a HASH. You should also review your code to see if the existing image_id and image_id2 indexes are getting used at all. –  symcbean Aug 19 '13 at 14:03
    
Hey thanks for the help, I have added the indexes and listed above. Can you see if what I have done is right, I have never had to work with indexes before. –  Shutupsquare Aug 19 '13 at 15:52
    
For this query (unless your data is rather skewed) the order should be date, image_id, likes - that's not what you've got here. All other things being equal, fields used in filtering should come before fields added to get covering queries. Fields with higher cardinality should come first but given the nature of the query, although image_id has a higher cardinality than date, a negated match means image_id is not effective for lookup (except in the case where it's a covering index) in this case, it would be more effective to put date first. –  symcbean Aug 19 '13 at 20:56
    
Hey thanks, after implementing and index on the fields you said, the load has come right down. Thanks now i'm off to learn about indexes. –  Shutupsquare Aug 22 '13 at 19:27

Is this table indexed?

SHOW INDEX FROM post_files_likes;

How many rows were returned by this query (without SUM)?

Is your my.cnf optimized for this?

Try to tune parameters for optimal configuration, at least with this: https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

share|improve this answer
    
Hey thanks, I have added the output of SHOW INDEX FROM post_files_likes –  Shutupsquare Aug 19 '13 at 13:25
    
Now it looks fine and i recommend to tune mysql too. –  GioMac Aug 19 '13 at 23:12

From your query plan:

Using where; Using temporary; Using filesort

This is going to suck. It's going to use a temporary, on-disk table.

The likely cause of this is that mysql is using the key image_id_2, which does not include the date, this is because you use DATE_SUB(...) instead of a simple comparison. Try precalculating the cutoff date in your code and using date >= that-date-here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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