StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POmysql query performance help
text
Body
copied!<p>I have a quite large table storing words contained in email messages </p> <pre><code>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 | | +----------------+---------+------+-----+---------+----------------+ </code></pre> <p>table contains about 100M rows<br> mwr_message_id is a FK to messages table<br> mwr_word_id is a FK to words table<br> mwr_count is the number of occurrencies of word mwr_word_id in message mwr_message_id </p> <p>To calculate most used words, I use the following query</p> <pre><code>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; </code></pre> <p>that runs almost forever (more than half an hour on the test server) </p> <pre><code>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) </code></pre> <p>Is there anything I can do to "speed up" the query (apart from adding more ram, more cpu, faster disks)?</p> <p>thank you in advance<br> stefano</p> <p>P.S. EXPLAIN result:</p> <pre><code>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) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload