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.

score 6 · Accepted Answer · edited May 23 '17 at 11:33

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 
;

score 0 · Answer 2 · edited May 14 '12 at 19:52

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

score 0 · Answer 3 · answered May 14 '12 at 20:05

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

slow query with fulltext and left join

3 Answers3

Linked