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
add comment

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.