Evitare filesort con COUNT

[..:: T€O ::..]

Salve ragazzi,
ho due tabelle: NEWS e COMMENTS e creo una tabella che mi raccolga le notizie più commentate così:

SELECT NEWS.ID_NEWS,
    (SELECT COUNT(*) FROM COMMENTS WHERE NEWS.ID_NEWS = COMMENTS.ID_NEWS) AS NUM_COMMENTI
FROM NEWS
WHERE NEWS.LANG_ita = 0 AND NEWS.TIMESTAMP >= DATE_SUB('2013-01-09', INTERVAL 1 WEEK)
ORDER BY NUM_COMMENTI DESC
LIMIT 0,5

Mi tira fuori correttamente l'id della news e accanto i commenti, sono le ultime 5 ed è tutto giusto. Solo che no ho notato che facendo Explain mi fa un filesort.
A questo punto mi chiedo, dato che mi sto scervellando da tutta la sera, è inevitabile avere un filesort quando si fa un count e c'è da ordinare le righe?
Ho messo gli indici sia su News che su Comments ma il problema sta in quell'ORDER BY NUM_COMMENTS, che è un numero che non ho in nessuna tabella fisicamente.

Grazie mille

[vnt54]

ORDER BY NUM_COMMENTS

E' un errore di trascrizione?non lo vedo da nessuna parte.

[..:: T€O ::..]

Originariamente inviato da vnt54
E' un errore di trascrizione?non lo vedo da nessuna parte.

si scusami, ovviamente ORDER BY NUM_COMMENTI (creato dalla SELECT)... E per questo ordinando per questa variabile non so come evitare il filesort.... Vi prego sto impazzendo

[optime]

metti le due tabelle in JOIN e fai la COUNT / GROUP BY

[..:: T€O ::..]

Originariamente inviato da optime
metti le due tabelle in JOIN e fai la COUNT / GROUP BY

Già provato:

SELECT NEWS.ID_NEWS, COUNT(*) AS NUM_COMMENTI
FROM NEWS
INNER JOIN COMMENTS ON NEWS.ID_NEWS = COMMENTS.ID_NEWS
WHERE NEWS.LANG_ita = 0 AND NEWS.TIMESTAMP >= DATE_SUB('2013-01-09', INTERVAL 1 WEEK)
GROUP BY NEWS.ID_NEWS
ORDER BY NUM_COMMENTI DESC
LIMIT 0,5

anche:

SELECT NEWS.ID_NEWS, SUM(ISNULL(COMMENTS.ID_NEWS)=0) as NUM_COMMENTI
FROM NEWS
INNER JOIN COMMENTS ON COMMENTS.ID_NEWS = NEWS.ID_NEWS
WHERE NEWS.LANG_ita = 0 AND NEWS.TIMESTAMP >= DATE_SUB('2013-01-09', INTERVAL 1 WEEK)
GROUP BY NEWS.ID_NEWS
ORDER BY NUM_COMMENTI DESC
LIMIT 0,5

e pure così:

SELECT NEWS.ID_NEWS, tmp.NUM_COMMENTI
FROM NEWS
INNER JOIN (SELECT ID_NEWS, COUNT(*) AS NUM_COMMENTI FROM COMMENTS GROUP BY COMMENTS.ID_NEWS) AS tmp
ON NEWS.ID_NEWS = tmp.ID_NEWS
WHERE NEWS.LANG_ita = 0 AND NEWS.TIMESTAMP >= DATE_SUB('2013-01-09', INTERVAL 1 WEEK)
ORDER BY tmp.NUM_COMMENTI DESC
LIMIT 0,5

(ma questa va a cercare tra troppe righe)

funzionano, però in queste maniere peggioro:

Using where; Using temporary; Using filesort

Nonostante abbia l'indice LANG_ita che comprende ID_NEWS e TIMESTAMP che comprende ID_NEWS

[franzauker2.0]

Posto che l'explain mostra che usi mysql o mariadb, segnalo che l'utilizzo di una subquery dipendente (primo caso) richiede abbastanza ovviamente un file sort.
Nel secondo invece (join) tipicamente si avrà una temporary table.

Perchè accade? Accade perchè non esiste un indice sul dato che stai calcolando al volo (il count), in qualsiasi modo lo vuoi generare.
Pertanto, quando li vuoi ordinare, ci vorrà necessariamente un "meccanismo" per ordinarli DOPO averli ottenuti.

Non mi è chiarissimo su come ti aspetti che, magicamente, esista un indice (e che quindi non richieda uno stage di ordinamento successivo) su un dato che nel database... non c'è.

Forse ho capito male?

[..:: T€O ::..]

Ah ecco, in ogni caso il filesort è obbligatorio quindi..... mi basta questa risposta per mettermi l'anima in pace

[franzauker2.0]

Originariamente inviato da ..:: T€O ::..
Ah ecco, in ogni caso il filesort è obbligatorio quindi..... mi basta questa risposta per mettermi l'anima in pace

In realtà la risposta te l'eri già data
Ho messo gli indici sia su News che su Comments ma il problema sta in quell'ORDER BY NUM_COMMENTS, che è un numero che non ho in nessuna tabella fisicamente.Solo che non volevi "convincerti"

Proviamo a ragionare (se ti va) un attimo su come dovrebbe funzionare un ottimizzatore che fa quello che vuoi.

Cioè tu, intelligente e non stupido come l'elaboratore, riesci a studiare un meccanismo per il quale conti le righe ottenendole ordinate per cardinalità?

(spero che coglierai l'intento maieutico).

Se non ci riesci, dubita fortissimamente che qualcosa di stupido come il planner riesca a far meglio (oddio sto dando per scontato che tu abbia più capacità dell'ottimizzatore, questo in generale dipende dall'esperienza)... di te.

[..:: T€O ::..]

Originariamente inviato da franzauker2.0
In realtà la risposta te l'eri già data
Ho messo gli indici sia su News che su Comments ma il problema sta in quell'ORDER BY NUM_COMMENTS, che è un numero che non ho in nessuna tabella fisicamente.Solo che non volevi "convincerti"

Proviamo a ragionare (se ti va) un attimo su come dovrebbe funzionare un ottimizzatore che fa quello che vuoi.

Cioè tu, intelligente e non stupido come l'elaboratore, riesci a studiare un meccanismo per il quale conti le righe ottenendole ordinate per cardinalità?

(spero che coglierai l'intento maieutico).

Se non ci riesci, dubita fortissimamente che qualcosa di stupido come il planner riesca a far meglio (oddio sto dando per scontato che tu abbia più capacità dell'ottimizzatore, questo in generale dipende dall'esperienza)... di te.

Eheheheh perfetto, ho colto in pieno

[franzauker2.0]

Per quanto riguarda il tuo problema, in generale, considera se è vantaggioso mantenere brutalmente una tabella con i "top 5", lato programma, o lato trigger.
Considera che data la natura del limit che hai imposto deve comunque ordinare tutti i risultati, per poi scartarne la maggior parte.
Ne segue che se il "group by" ha cardinalità relativamente piccola, "paghi" poco o quasi niente per l'ordinamento (e viceversa)