Re: Get rid of Temporary/filesort, rather simple query

[Rick James]

ALTER TABLE pm2user
  DROP KEY index2,
  ADD PRIMARY KEY(`user_id`,`folder_id`),
  ADD INDEX (`conversation_uuid`);

SELECT
     SUM(p2u.last_viewed<pm.datetime) AS unread_count,
     pm.conversation_uuid,
     MAX(pm.subject) AS sample_subject,
     MAX(pm.datetime) AS latest_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
     latest_datetime DESC
LIMIT
     0, 15;

Rationale...

* You had no decent index for doing
AND p2u.user_id=1
AND p2u.folder_id=1
which is what the query would like to start with.

* uuid+user is unique, so it may as well be the PRIMARY KEY, thereby avoiding the artificial creation of a BIGINT PK (as InnoDB will do).

* Assuming you have other queries need to reach into pm2user via the uuid, I added an index for that.

* Because of the GROUP BY, you were getting random results for 3 of the selects. I added aggregates to make it less random.

* I changed the ORDER BY -- you can't really order by something that is eliminated by the GROUP BY.