php_nub_qq php_nub_qq - 6 months ago 13
SQL Question

Optimize table to avoid using temporary and using filesort

I have a messages table

CREATE TABLE `messages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author` int(11) DEFAULT NULL,
`time` int(10) unsigned DEFAULT NULL,
`text` text CHARACTER SET latin1,
`dest` int(11) unsigned DEFAULT NULL,
`type` tinyint(4) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `author` (`author`),
KEY `dest` (`dest`)
) ENGINE=InnoDB AUTO_INCREMENT=2758 DEFAULT CHARSET=utf8;


I need to get messages between two users

SELECT
...
FROM
`messages` m
LEFT JOIN `people` p ON m.author = p.id
WHERE
(author = 1 AND dest = 2)
OR (author = 2 AND dest = 1)
ORDER BY
m.id DESC
LIMIT 0, 25


When I EXPLAIN this query I get

enter image description here

Please excuse any ignorance, but is there a way I could optimize this table to avoid using a temporary table and filesort for this query, for now it is not causing a problem but I'm pretty sure in future it is going to be troublesome?

Answer

First, I'm guessing the left join is not necessary. Second, consider using union all instead. Then one approach is:

(SELECT ...
 FROM messages m JOIN
      people p
      ON m.author = p.id
 WHERE author = 1 AND dest = 2
 ORDER BY id DESC
 LIMIT 25
)
(SELECT ...
 FROM messages m JOIN
      people p
      ON m.author = p.id
 WHERE author = 2 AND dest = 1
 ORDER BY id DESC
 LIMIT 25
)
ORDER BY m.id DESC
LIMIT 0, 25

With this query, an index on messages(author, dest, id) should make it fast. (Note: you might need to include m.id in the SELECT list.)