mysql query performance help
- by Stefano
Hi
I have a quite large table storing words contained in email messages
mysql> explain t_message_words;
+----------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+----------------+
| mwr_key | int(11) | NO | PRI | NULL | auto_increment |
| mwr_message_id | int(11) | NO | MUL | NULL | |
| mwr_word_id | int(11) | NO | MUL | NULL | |
| mwr_count | int(11) | NO | | 0 | |
+----------------+---------+------+-----+---------+----------------+
table contains about 100M rows
mwr_message_id is a FK to messages table
mwr_word_id is a FK to words table
mwr_count is the number of occurrencies of word mwr_word_id in message mwr_message_id
To calculate most used words, I use the following query
SELECT SUM(mwr_count) AS word_count, mwr_word_id
FROM t_message_words
GROUP BY mwr_word_id
ORDER BY word_count DESC
LIMIT 100;
that runs almost forever (more than half an hour on the test server)
mysql> show processlist;
+----+------+----------------+--------+---------+------+----------------------+-----------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info
+----+------+----------------+--------+---------+------+----------------------+-----------------------------------------------------
processlist
| 41 | root | localhost:3148 | tst_db | Query | 1955 | Copying to tmp table | SELECT SUM(mwr_count) AS word_count, mwr_word_id
FROM t_message_words
GROUP BY mwr_word_id |
+----+------+----------------+--------+---------+------+----------------------+-----------------------------------------------------
3 rows in set (0.00 sec)
Is there anything I can do to "speed up" the query (apart from adding more ram, more cpu, faster disks)?
thank you in advance
stefano
