5

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.

5

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:

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
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

  • 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
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
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.