MySql: evitare count(*) in query lente

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

Salve ragazzi, possiedo un sito da 100.000 visitatori giornalieri.
Il sito é costruito custom, senza alcun CMS.
Ho un DB in MySql che contiene quasi 100.000 post e quasi 1.000.000 di commenti degli utenti sparsi in questi post.
Va da se che in una home, ad esempio, io debba mostrare il Titolo del post e sicuramente quanti commenti ha quel singolo post. Ho indicizzato tutti gli indici, non ho nemmeno un filesort ma con il passare del tempo la Query di elenco post che annida una select count(*) all'interno comincia a rallentare di millisecondi che vanno pian piano a lockarmi le table per le visite successive.
la mia query più o meno é così:

SELECT post.title,
(SELECT COUNT(*) FROM comments WHERE comments.post_id = post.id) as num_commenti
FROM post
ORDER BY post.timestamp DESC 

Funziona ma sta iniziando a rallentare. Cosa consigliate? Io pensavo di andarmi a fare una colonna in post chiamata direttamente num_commenti e andare ad aggiornarla col +1 ogni commento che viene inserito però ho qualche dubbio a riguardo... Ad esempio cosa succede se due in contemporanea scrivono? O se cancello un commento? Devo andare a fare -1 col rischio di inceppare un meccanismo automatico...

Grazie mille

[bomberdini]

L'evento del +1 e -1 e' percorribile tramite trigger.

After Insert fai +1
After Delete fai -1

[sspintux]

...
con il passare del tempo la Query di elenco post che annida una select count(*) all'interno comincia a rallentare di millisecondi che vanno pian piano a lockarmi le table per le visite successive.
....

se per te non è un problema, potresti anche valutare se eseguire la query con un livello di isolamento "READ UNCOMMITTED"
che non dovrebbe coinvolgere nessun blocco di tabelle;
l'inconveniente, ammesso che per te lo sia , sarebbe appunto la lettura di dati non ancora "consolidati"

P.S.
su come eseguire la query con "READ UNCOMMITTED" (che suppongo sia sicuramente supportato da MySQl)
dovrei vedere non essendo esperto di MySql, ma tanto vale che ti informi tu e poi ci fai sapere

HTH

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

Guarda, proprio ieri sera stavo vedendo il READ UNCOMMITTED in una classica query via PHP. In serata mi ci metto, ma se qualcuno sa se è possibile scriva. Mi va bene anche avere dati non "consolidati". Intanto li metto, anche imprecisi che siano.

[optime]

metti le due tabelle in JOIN e conta i commenti con una Group By

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

Ti ringrazio, ho provato ma va ancora più lento del COUNT(*) in una SELECT annidata.. Qualche millisecondo in più.