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

[Rick James]

I'm still struggling with your [mis]use of GROUP BY. There are multiple rows in each table for a given conversation_uuid, right? This will pick ONE of them to evaluate:
IF(p2u.last_viewed<pm.datetime,true,false) AS unread
It will not check all the last_viewed's.
maybe MIN(last_viewed) < MAX(datetime)? Or some other _aggregrate_ that will rummage through all the dates for the whole conversation?

What is a PM?

Sounds to me like a "conversation" is a "thread".

Can you say in words what the SELECT is supposed to do? I am having too much trouble with the GROUP BY to understand it from the SQL.