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
