Hello,
I recently ugraded to 4-14-Max from 4.13-Max to take advantage
of the optimizer fulltext bug fix. However a certain query using the
fulltext indexies is MUCH SLOWER then using no idex. Here is the query
select distinct p.USER_CREATED, p.ID, p.DATE_CREATED, p.LAST_CLICK, p.ZIP
from PERSON p
join INTEREST i on p.ID=i.PERSON_ID
join INTEREST i0 on p.ID=i0.PERSON_ID
join INTEREST_TYPE it0 on i0.INTEREST_ID=it0.ID
where p.FIRST_NAME!='Unsubscribed' and match(i.COMMENT) against('+games ' in
boolean mode) and (it0.NAME='occupation' and match(i0.COMMENT)
against('+software ' in boolean mode));
the explain on 4.0.13-Max
+-------+--------+----------------------------------------------------------
---+-----------+---------+-------------+--------+---------------------------
---+
| table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+-------+--------+----------------------------------------------------------
---+-----------+---------+-------------+--------+---------------------------
---+
| it0 | ALL | PRIMARY
| NULL | NULL | NULL | 30 | Using where; Using temporary
|
| i | ALL |
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx | NULL |
NULL | NULL | 550709 | Using where |
| p | eq_ref | PRIMARY,person_id_base_idx
| PRIMARY | 40 | i.PERSON_ID | 1 | Using where
|
| i0 | eq_ref |
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx | PERSON_ID |
80 | p.ID,it0.ID | 1 | Using where; Distinct |
Query time: 15 seconds
Notice that it does not use the fulltext index (COMMENT) in the interest
tables I,i0
The explain on 4-14-Max
+-------+----------+--------------------------------------------------------
-------------+---------+---------+----------------+------+------------------
------------+
| table | type | possible_keys
| key | key_len | ref | rows | Extra |
+-------+----------+--------------------------------------------------------
-------------+---------+---------+----------------+------+------------------
------------+
| i | fulltext |
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx,COMMENT |
COMMENT | 0 | | 1 | Using where; Using temporary |
| p | eq_ref | PRIMARY,person_id_base_idx
| PRIMARY | 40 | i.PERSON_ID | 1 | Using where |
| i0 | fulltext |
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx,COMMENT |
COMMENT | 0 | | 1 | Using where; Distinct |
| it0 | eq_ref | PRIMARY
| PRIMARY | 40 | i0.INTEREST_ID | 1 | Using where; Distinct |
Query time : 146 seconds, ( slow querylog lists rows examined as 3.6 million
)
Notice the different join order and use of the fulltext indexies
With a single join of the INTEREST table the index is used correctly and the
query is quick. If anyone has any idea why USING a fulltext index is slower
please share your knowledge. Below is the show create table output.
Thanks,
Trevor
| 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 |
| INTEREST_TYPE | CREATE TABLE `INTEREST_TYPE` (
`ID` varchar(40) NOT NULL default '',
`NAME` varchar(100) default NULL,
`NICE_NAME` varchar(100) default NULL,
`TYPE` varchar(20) NOT NULL default '',
`DESCRIPTION` mediumtext,
`DATE_CREATED` timestamp(14) NOT NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAM |
| PERSON |CREATE TABLE `PERSON` (
`ID` char(40) NOT NULL default '',
`PHOTO_ID` char(40) default NULL,
`EMAIL` char(100) NOT NULL default '',
`LAST_CLICK` timestamp(14) NOT NULL,
`FIRST_NAME` char(40) default NULL,
`LAST_NAME` char(40) default NULL,
`ZIP` char(15) NOT NULL default '',
`COUNTRY` char(40) default NULL,
`BIRTHDAY` datetime default NULL,
`GENDER` char(1) default NULL,
`STATUS` char(1) default NULL,
`SEND_EMAIL` tinyint(1) default NULL,
`IS_DELETED` char(1) NOT NULL default '',
`USER_CREATED` int(11) NOT NULL default '0',
`DATE_CREATED` timestamp(14) NOT NULL,
`USER_MODIFIED` int(11) default NULL,
`DATE_MODIFIED` timestamp(14) NOT NULL,
`HIDE_AGE` tinyint(1) default NULL,
`HIDE_GENDER` tinyint(1) default NULL,
`HIDE_LOCATION` tinyint(1) default NULL,
`HIDE_ONLINE` tinyint(1) default NULL,
`GENERATION` int(11) default '0',
`ALLOW_EMAIL_DEGREE` int(11) default '0',
`HAS_FILTER` tinyint(1) default '0',
`NEWSLETTER` tinyint(3) unsigned default NULL,
PRIMARY KEY (`ID`),
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 |
