slow query with fulltext and left join

Question

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.

Answer 1

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 
;

Answer 2

This are my tips, if someone want to add something or let me know if I'm wrong I'll appreciate it:

• Remove the * from the select statement, just put the columns that you need.

  SELECT COLUMN1,COLUMN2,COLUMN3
  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
  

• If you don't need all the results you can add limit at the end,

  ORDER BY table3.pid 
  limit 100;
  

• If you can try with a like (probably is not faster, but I'm just curious) and let us know how it goes would be great!.. Regards

Answer 3

Thanks all, also this method without left join, could get a faster query, 0.12 seconds:

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