Recently we are facing a slowdown on our Q2A site and mostly it is due to the below update query to qa_words table. Is there a good workaround to get around this? "show processlist" in the MySQL console often shows this same process.
| 29984267 | qauser | localhost:56462 | qa | Query | 1 | executing | UPDATE qa_words AS x, (SELECT qa_words.wordid, COUNT(qa_contentwords.wordid) AS contentcount FROM qa_words LEFT JOIN qa_contentwords ON qa_contentwords.wordid=qa_words.wordid WHERE qa_words.wordid IN (63,38178,52,7588,9810,617,170424,1049,105,6375,1622,905,845,888,592,41033,679,294,4550,61,38,3699,4,224,7,1162,13,264,14,688,1118,145,220,2736,1318,1353,59,5203,26664,28855,27739,28962) GROUP BY wordid) AS a SET x.contentcount=a.contentcount WHERE x.wordid=a.wordid |
mysql> select count(*) from qa_words;
+----------+
| count(*) |
+----------+
| 146314 |
+----------+
