I have one table with a full text index, and another table to which I need to join, based on an ID field in the fulltext table. I can't get the join query to use non-fulltext table's index to do a ref join; it insists on doing an index scan on the second table. Is there any way to avoid the index scan?
The query is
SELECT
dm.MessageID, sm.DBID
FROM
tdistinctmessages dm,
tSite_messages sm
WHERE MATCH (dm.message) AGAINST ('"microsoft support"' IN BOOLEAN MODE)
AND dm.MessageID=sm.MessageID
Table definitions are:
CREATE TABLE `tdistinctmessages` (
`message` VARCHAR(150) COLLATE latin1_general_ci NOT NULL,
`MessageID` INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`message`,`MessageID`),
FULLTEXT KEY `FTIX_Message` (`message`)
) ENGINE=MYISAM AUTO_INCREMENT=26935581 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
CREATE TABLE `tsite_messages` (
`DBID` INT(10) NOT NULL,
`wordID` BIGINT(20) UNSIGNED DEFAULT NULL,
`MessageID` INT(10) DEFAULT NULL,
`Repetition` INT(10) NOT NULL DEFAULT '0',
`Emphasis` INT(10) NOT NULL DEFAULT '0',
KEY `IX_DBID_WordID_MessageID_
Repetition
` (`DBID`,`wordID`,`MessageI
D`,`Repeti
tion`),
KEY `NewIndex1` (`MessageID`,`DBID`,`Repet
ition`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
EXPLAIN IS
possible_keys key key_len ref rows Extra
1 SIMPLE dm fulltext FTIX_Message FTIX_Message 0 1 Using where
1 SIMPLE sm index NewIndex1 NewIndex1 13 \N 27892950 Using where; Using index