Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a performance problem when selecting rows containing text fields:

SELECT id FROM message
WHERE NOT(message.sender_id = 368352 AND message.is_deleted_sender = 1)
    AND NOT(message.recipient_id = 368352 AND message.is_deleted_recipient = 1)
    AND recipient_id = 368352  AND is_spam = 0 AND is_deleted_recipient = 0
ORDER BY   timestamp desc  LIMIT 0, 5;

5 rows in set (0.07 sec)

SELECT message FROM message
WHERE NOT(message.sender_id = 368352 AND message.is_deleted_sender = 1)
    AND NOT(message.recipient_id = 368352 AND message.is_deleted_recipient = 1)
    AND recipient_id = 368352  AND is_spam = 0 AND is_deleted_recipient = 0
ORDER BY   timestamp desc  LIMIT 0, 5;

5 rows in set (0.15 sec)

Explain is the same for both queries:

mysql> EXPLAIN SELECT id FROM message WHERE NOT(message.sender_id = 368352 AND message.is_deleted_sender = 1) AND NOT(message.recipient_id = 368352 AND message.is_deleted_recipient = 1)  AND recipient_id = 368352  AND is_spam = 0 AND is_deleted_recipient = 0 ORDER BY   timestamp desc  LIMIT 0, 5;
+----+-------------+---------+------+---------------------------------------+--------------+---------+-------------------+------+-----------------------------+
| id | select_type | table   | type | possible_keys                         | key          | key_len | ref               | rows | Extra                       |
+----+-------------+---------+------+---------------------------------------+--------------+---------+-------------------+------+-----------------------------+
|  1 | SIMPLE      | message | ref  | sender_id,recipient_id,recipient_id_2 | recipient_id | 6       | const,const,const |   86 | Using where; Using filesort |
+----+-------------+---------+------+---------------------------------------+--------------+---------+-------------------+------+-----------------------------+

Indexes (I know there's maybe no need for recipient_id index when there's recipient_id_2, but that's what I have there):

mysql> show indexes from message;
+---------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| message |          0 | PRIMARY        |            1 | id                   | A         |    14693109 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | thread_id      |            1 | thread_id            | A         |     4897703 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | sender_id      |            1 | sender_id            | A         |      253329 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | sender_id      |            2 | is_spam              | A         |      267147 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | sender_id      |            3 | is_deleted_sender    | A         |      333934 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | recipient_id   |            1 | recipient_id         | A         |      272094 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | recipient_id   |            2 | is_spam              | A         |      277228 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | recipient_id   |            3 | is_deleted_recipient | A         |      445245 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | timestamp      |            1 | timestamp            | A         |    14693109 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | recipient_id_2 |            1 | recipient_id         | A         |      206945 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | recipient_id_2 |            2 | is_deleted_sender    | A         |      277228 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | recipient_id_2 |            3 | is_spam              | A         |      277228 |     NULL | NULL   |      | BTREE      |         |
| message |          1 | recipient_id_2 |            4 | is_chat              | A         |      341700 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+

How do I deal with that? I can even do something like

SELECT message.message FROM message,
    (SELECT id FROM message 
     WHERE NOT(message.sender_id = 368352 AND message.is_deleted_sender = 1)
         AND NOT(message.recipient_id = 368352 AND message.is_deleted_recipient = 1)         
         AND recipient_id = 368352  AND is_spam = 0 AND is_deleted_recipient = 0      
     ORDER BY   timestamp desc  LIMIT 0, 5) m
 WHERE m.id = message.id;

and it runs faster: 5 rows in set (0.06 sec)

My guess is it has something to do with filesort - larger data is sorted slower or something. I copied that database from production to another server to experiment with but strangely enough it runs fast in both cases.

share|improve this question

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.