Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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 
;
share|improve this answer
    
You rock dude :) –  bksi Mar 3 '13 at 0:57
add comment

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
share|improve this answer
    
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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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