Hi guys,
I'm having problems optimizing this query. I can't get rid of the Using temporary; Using filesort. I'm sure is a simple task for you though :)
Any help is very appreciated, thanks!
SELECT
IF(p2u.last_viewed<pm.datetime,true,false) AS unread,
pm.id,
pm.conversation_uuid,
pm.subject,
pm.datetime
FROM
pm2user AS p2u,
pm
WHERE
p2u.conversation_uuid=pm.conversation_uuid
AND p2u.user_id=1
AND p2u.folder_id=1
GROUP BY
p2u.conversation_uuid
ORDER BY
pm.id DESC
LIMIT
0, 15;
The EXPLAIN result is as follows:
1, 'SIMPLE', 'p2u', 'index', 'index2', 'index2', '52', '', 31728, 'Using where; Using index; Using temporary; Using filesort'
1, 'SIMPLE', 'pm', 'ref', 'uuid_datetime', 'uuid_datetime', '36', 'p2u.conversation_uuid', 2, ''
And here are the current indices:
[uuid_datetime]
'pm', 1, 'uuid_datetime', 1, 'conversation_uuid', 'A', 41856, , '', '', 'BTREE', ''
'pm', 1, 'uuid_datetime', 2, 'datetime', 'A', 125569, , '', '', 'BTREE', ''
[index2]
'pm2user', 1, 'index2', 1, 'conversation_uuid', 'A', 42303, , '', '', 'BTREE', ''
'pm2user', 1, 'index2', 2, 'user_id', 'A', 42303, , '', '', 'BTREE', ''
'pm2user', 1, 'index2', 3, 'folder_id', 'A', 42303, , '', '', 'BTREE', ''
'pm2user', 1, 'index2', 4, 'last_viewed', 'A', 42303, , '', '', 'BTREE', ''