MySQL:使用连接多次搜索同一个表

Question

我正在编写一个脚本来为用户对消息进行部分词搜索。每个对话都有一个 mail_id，每个单独的消息都有一个 msg_id。

我有一个表 mail_word_index，其中包含一条消息中的每个单词的一行。

CREATE TABLE IF NOT EXISTS `mail_word_index` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sender_id` int(10) unsigned NOT NULL DEFAULT '0',
  `dest_id` int(10) unsigned NOT NULL DEFAULT '0',
  `mail_id` int(10) unsigned NOT NULL DEFAULT '0',
  `msg_id` int(10) unsigned NOT NULL DEFAULT '0',
  `word` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `dest_id` (`dest_id`,`word`),
  KEY `sender_id` (`sender_id`,`word`),
  KEY `multiple_words` (`mail_id`,`msg_id`,`word`)
) ENGINE=MyISAM ;

我有一个查询需要 0.01 秒才能完成

SELECT DISTINCT w1.mail_id FROM mail_word_index AS w1,
mail_word_index AS w2 
WHERE w1.sender_id=1 
AND w1.word LIKE 'str%' 
AND w1.mail_id=w2.mail_id 
AND w1.msg_id=w2.msg_id 
AND w2.word LIKE 'con%' LIMIT 20

然而，一次搜索一个单词只需要 0.002 秒完成每个单词，总共需要 0.004 秒:

SELECT DISTINCT w1.mail_id FROM mail_word_index AS w1 
WHERE w1.sender_id=1 AND w1.word LIKE 'str%' LIMIT 20

SELECT DISTINCT w1.mail_id FROM mail_word_index AS w1 
WHERE w1.sender_id=1 AND w1.word LIKE 'con%' LIMIT 20

内部连接似乎减慢了第一个查询。我将如何更改第一个查询以使其更快？

EXPLAIN 告诉我:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  w1  range   sender_id,multiple_words    sender_id   21  NULL    1   Using where; Using temporary
1   SIMPLE  w2  ref multiple_words  multiple_words  8   game-node4.w1.mail_id,game-node4.w1.msg_id  8   Using where; Using index; Distinct

Answer 1

在这种情况下，为 w1 创建索引 multi:

ALTER TABLE `mail_word_index`
ADD INDEX `multi` USING BTREE (`sender_id`, `mail_id`, `msg_id`, `word`) ;