<p>我有一个拥有超过3B行的大表格，我的查询现在需要3天才能执行。</p> <p>这是我的查询：</p> <pre class="brush:php;toolbar:false;">insert into frequencywords (word, frequency, filename) select word, count(*) as frequency, max(filename) from allwordstemp group by word</pre> <p>基本上，我的查询是从allwordstemp表格按照word进行分组，并且我想知道当frequency = 1时的<strong>filename</strong>，这就是为什么我添加了max(filename)，因为filename需要包含在聚合函数中，比如max。如果frequency > 1，我也不需要filename的值。 这两个表格都在word和filename上有2个索引。</p> <p>allwordstemp表格（filename是一个id文件）：</p> <pre class="brush:php;toolbar:false;">CREATE TABLE `allwordstemp` ( `word` varchar(45) DEFAULT NULL, `filename` int(11) DEFAULT NULL, KEY `idx_allwordstemp_word` (`word`), KEY `idx_allwordstemp_filename` (`filename`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;</pre> <p>frequencywords表格：</p> <pre class="brush:php;toolbar:false;">CREATE TABLE `frequencywords` ( `word` varchar(45) DEFAULT NULL, `frequency` int(11) DEFAULT NULL, `filename` int(11) DEFAULT NULL, KEY `idx_frequencywords_word` (`word`), KEY `idx_frequencywords_frequency` (`frequency`), KEY `idx_frequencywords_filename` (`filename`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;</pre> <p>解释选择：</p> <pre class="brush:php;toolbar:false;">+----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------------+----------+-------------+ | 1 | SIMPLE | allwordstemp | NULL | index | NULL | idx_allwordstemp_word | 138 | NULL | 3487864881 | 100.00 | Using index | +----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------------+----------+-------------+</pre> <p>我该如何使我的查询更快？</p>

select word, max(filename)
from allwordstemp
where frequency = 1
group by word