slow query with fulltext and left join

I have 3 tables

• about 250,000 records.

• table1 is MyISAM, add fullindex with title and content, add index with pid.

• table2 and table3 are InnoDB, add index with pid.

Only query table3, just cost 0.04 seconds.

select * from table3 
WHERE MATCH (title,content)
AGAINST ('+words' IN BOOLEAN MODE)
ORDER BY pid

but query like this, cost 16.87 seconds.

SELECT * 
FROM table1
INNER JOIN table2 ON table1.pid = table2.pid
LEFT JOIN table3 ON table1.pid = table3.pid
WHERE MATCH (table3.title, table3.content)
AGAINST ('+words' IN BOOLEAN MODE)
ORDER BY table3.pid

I make an EXPLAIN plan for the 2nd query, return:

id select_type  table     type  possible_keys    key    key_len    ref               rows      Extra
1  SIMPLE       table1    ALL    pid             NULL   NULL       NULL              201497    Using temporary; Using filesort
1  SIMPLE       table2    ref    pid             pid    32         mydb.table1.pid   1     
1  SIMPLE       table3    ref    pid             pid    32         mydb.table2.pid   222309    Using where

• Why is the 2nd query slow?

• How can I optimize it?

Thanks.

Unfortunately, MySQL is behaving exactly as I expected.

The problem lies in the fact that MySQL's Query Optimizer gets very confused when used in conjunction with other tables in an EXPLAIN plan's compliation. I wrote about this before:

• https://stackoverflow.com/a/6092216/491757 (May 23, 2011)

• FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional (Oct 25, 2011)

• Mysql fulltext search my.cnf optimization (Jan 26, 2012)

• MySQL EXPLAIN doesn't show 'use index' for FULLTEXT (May 07, 2012)

My suggestion: Try Refactoring the Query By Getting the FULLTEXT Search to occur before JOINs

SELECT * FROM table1 
INNER JOIN table2 ON table1.pid = table2.pid 
LEFT JOIN
(
    select * from table3  
    WHERE MATCH (title, content) 
    AGAINST ('+words' IN BOOLEAN MODE )
    ORDER BY pid 
) table3
ON table1.pid = table3.pid 
;

StackExchange Database Administrators Q#17839, answer score: 6