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 
;

Is that still the case? Is that in current version been fixed? — alacambra, Oct 28 '19 at 14:52

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

0

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

edited May 14 '12 at 19:52

ypercubeᵀᴹ

97,895
13
214
305

answered May 14 '12 at 19:03

jcho360

1,999
8
23
31

LIMIT does not necessarily make a query faster. It depends on whether it has to gather all the rows before performing the ORDER BY -- This query is complex enough so it is the case. When an INDEX can do all the WHERE and all the ORDER BY, then LIMIT may have a dramatic speedup. The index being used is the FULLTEXT index, which does not help with LEFT or ORDER BY. – Rick James Aug 30 '18 at 23:43
LIKE would be a disaster here since you would need (1) leading wildcards and (2) OR. No index would be useful, or at east not for the LIKE part. – Rick James Aug 30 '18 at 23:44

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

0

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

answered May 14 '12 at 20:05

cj333

189
2
11

This is equivalent to changing LEFT to INNER join. – ypercubeᵀᴹ May 14 '12 at 20:10
1

if you change * for the columns that you need the values only + using limit, I think you can decrease more those 0.12 sec. – jcho360 May 14 '12 at 20:31

slow query with fulltext and left join

3 Answers3

Linked