slow query with fulltext and left join
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 &#39;number of words&#39; conditional (Oct 25, 2011)
Mysql fulltext search my.cnf optimization (Jan 26, 2012)
MySQL EXPLAIN doesn&#39;t show &#39;use index&#39; 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 
;