Fellow Mysqlians,
Can anyone tell me why mysql refuses to use the fulltext index in the second query? Query 1; mysql> explain select straight_join distinct i.ID from INTEREST i use index (COMMENT) inner join PERSON p on p.ID=i.PERSON_ID where match(i.COMMENT) against('+todo ' in boolean mode); +-------+----------+----------------------------+---------+---------+------- ------+------+------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+----------+----------------------------+---------+---------+------- ------+------+------------------------------+ | i | fulltext | COMMENT | COMMENT | 0 | | 1 | Using where; Using temporary | | p | eq_ref | PRIMARY,person_id_base_idx | PRIMARY | 40 | i.PERSON_ID | 1 | Using index; Distinct | +-------+----------+----------------------------+---------+---------+------- ------+------+------------------------------+ 2 rows in set (0.00 sec) Query2 mysql> explain select straight_join distinct i.ID from INTEREST i use index (COMMENT) inner join PERSON p on p.ID=i.PERSON_ID inner join TURBINE_USER t on p.ID = t.LOGIN_NAME where match(i.COMMENT) against('+todo ' in boolean mode); +-------+--------+----------------------------+-----------------------+----- ----+-------------+--------+------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+----------------------------+-----------------------+----- ----+-------------+--------+------------------------------+ | i | ALL | NULL | NULL | NULL | NULL | 511322 | Using where; Using temporary | | p | eq_ref | PRIMARY,person_id_base_idx | PRIMARY | 40 | i.PERSON_ID | 1 | Using index; Distinct | | t | ref | turbine_user_name_idx | turbine_user_name_idx | 40 | p.ID | 1 | Using index; Distinct | The only difference between the 2 queries is the extra join. But with the straight_join and use index I don't understand why the fulltext was abandoned. Table info INTEREST | INTEREST | CREATE TABLE `INTEREST` ( `ID` varchar(40) NOT NULL default '', `PERSON_ID` varchar(40) NOT NULL default '', `COMMENT` mediumtext, `DATE_CREATED` timestamp(14) NOT NULL, `INTEREST_ID` varchar(40) NOT NULL default '', PRIMARY KEY (`ID`), UNIQUE KEY `PERSON_ID` (`PERSON_ID`,`INTEREST_ID`), KEY `interest_person_id_idx` (`PERSON_ID`), KEY `interest_person_id_two_idx` (`PERSON_ID`,`COMMENT`(200)), FULLTEXT KEY `COMMENT` (`COMMENT`) ) TYPE=MyISAM | TURBINE_USER TURBINE_USER | CREATE TABLE `TURBINE_USER` ( `USER_ID` int(11) NOT NULL auto_increment, `LOGIN_NAME` varchar(40) NOT NULL default '', `PASSWORD_VALUE` varchar(32) NOT NULL default '', `FIRST_NAME` varchar(99) NOT NULL default '', `LAST_NAME` varchar(99) NOT NULL default '', `EMAIL` varchar(99) default NULL, `CONFIRM_VALUE` varchar(99) default NULL, `MODIFIED` timestamp(14) NOT NULL, `CREATED` timestamp(14) NOT NULL, `LAST_LOGIN` timestamp(14) NOT NULL, `OBJECTDATA` mediumblob, PRIMARY KEY (`USER_ID`), UNIQUE KEY `EMAIL` (`EMAIL`), KEY `turbine_user_name_idx` (`LOGIN_NAME`) ) TYPE=MyISAM | PERSON KEY `person_id_base_idx` (`ID`), KEY `person_email_idx` (`EMAIL`), KEY `person_zip_idx` (`ZIP`), KEY `person_last_name_idx` (`LAST_NAME`), KEY `person_first_name_idx` (`FIRST_NAME`), KEY `person_generation_idx` (`GENERATION`), KEY `person_birthday_idx` (`BIRTHDAY`), KEY `person_user_created_idx` (`USER_CREATED`) ) TYPE=MyISAM | Many Thanks, Trevor