Salve,
mi sto imbattendo nella progettazione del DB per creare dei messaggi privati per il mio sito.
Ho creato la tabella che raggruppa la conversazione:

codice:
MESSAGGI_THREAD
--------------------
ID_THREAD
ID_AUTORE
TIMESTAMP
La tabella dei destinatari (che possono essere più di uno):

codice:
MESSAGGI_USER
--------------------
ID
ID_THREAD (che si collegherà a MESSAGGI_THREAD)
ID_USER
Ed in fine quella delle conversazioni vere e proprie:

codice:
MESSAGGI
--------------------
ID
ID_THREAD
ID_AUTORE
TESTO
TIMESTAMP


Fin qui tutto bene.
Ora, volendo simulare come Facebook, che entriamo nella nostra area privata e vogliamo vedere le "conversazioni" in cui partecipiamo, per fare l'elenco ho provato due soluzioni ma mi sto scervellando per evitare il maledetto filesort.

codice:
SELECT MESSAGGI_THREAD.ID_THREAD, MESSAGGI_THREAD.TIMESTAMP
FROM MESSAGGI_THREAD
LEFT JOIN MESSAGGI_USER ON MESSAGGI_USER.ID_THREAD = MESSAGGI_THREAD.ID_THREAD
WHERE MESSAGGI_USER.ID_USER = 4
ORDER BY MESSAGGI_THREAD.TIMESTAMP DESC
e

codice:
SELECT MESSAGGI_THREAD.ID_THREAD, MESSAGGI_THREAD.TIMESTAMP
FROM MESSAGGI_USER
LEFT JOIN MESSAGGI_THREAD ON MESSAGGI_THREAD.ID_THREAD = MESSAGGI_USER.ID_THREAD
WHERE MESSAGGI_USER.ID_USER = 4
ORDER BY MESSAGGI_THREAD.TIMESTAMP DESC
Queste due query fanno esattamente la stessa cosa, mi vanno a cercare le conversazioni che può leggere l'ID_USER 4 e le ordino per data di creazione della conversazione. Come posso fare per elencare queste benedette conversazioni ed evitare il filesort?

Il massimo sarebbe che me li ordina per ultimo messaggio (tabella MESSAGGI) scritto in quella conversazione, ci sono riuscito così ma è un ammucchio di filesort incredibili:
codice:
SELECT DISTINCT(M1.ID_THREAD),
    (SELECT TIMESTAMP FROM MESSAGGI M2 WHERE M2.ID_THREAD = M1.ID_THREAD ORDER BY TIMESTAMP DESC LIMIT 1) AS TIMESTAMP_ORDER
FROM MESSAGGI M1
LEFT JOIN MESSAGGI_USER M3 ON M3.ID_THREAD = M1.ID_THREAD
WHERE M3.ID_USER = 4
ORDER BY TIMESTAMP_ORDER DESC
Ho paura che quando i messaggi diventino tanti (e diventeranno tanti..) mi si rallenti tutto....
Gli indici li ho messi ma non capisco come posso evitare il filesort prendendo dati da una tabella e ordinandoli per la data che sta in un'altra.