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

内部联接似乎减慢了第一个查询的速度。如何更改第一个查询以使其更快？

解释告诉我：

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 创建索引多

：

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