mysql query performance help

Question

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

P.S. EXPLAIN result:

mysql> EXPLAIN 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;
+----+-------------+-----------------+-------+---------------+----------------------+---------+------+----------+---------------------------------+
| id | select_type | table           | type  | possible_keys | key                  | key_len | ref  | rows     | Extra                           |
+----+-------------+-----------------+-------+---------------+----------------------+---------+------+----------+---------------------------------+
|  1 | SIMPLE      | t_message_words | index | NULL          | IDX_t_message_words2 | 4       | NULL | 94823285 | Using temporary; Using filesort |
+----+-------------+-----------------+-------+---------------+----------------------+---------+------+----------+---------------------------------+
1 row in set (0.01 sec)

Answer 1

Perhaps an index on mwr_word_id would help. It's now part of a key, but you could add a key purely for that.

You could also increase the cache of mysql in the configuration file.

Answer 2

I did noy understand if you have an index by mwr_message_id and mwr_word_id, or just by mwr_word_id, or any other index other than the primary key one. If you don't have an index by mwr_word_id (or having mwr_word_id as first field) I suggest you add one.

If you already have such an index, and this is really a pain for being a very common scenario, I suggest you add some redundancy to words table that sums the total occurrences of mwr_word_id in all mwr_message_id.

And you can also add some triggers to t_message_words to handle the updates of this redundancy.

Answer 3

UPD: If you need to run this query one time - just wait for it completion. If you need to run this query a lot of times - create table with unique words and update counts in this table via trigger every insert/update/delete