How can I optimize MySQL query with multiple joins?

Question

Any inputs on how can I optimize joins in the MySQL query? For example, consider the following query

    SELECT E.name, A.id1, B.id2, C.id3, D.id4, E.string_comment
    FROM E
    JOIN A ON E.name = A.name AND E.string_comment = A.string_comment
    JOIN B ON E.name = B.name AND E.string_comment = B.string_comment
    JOIN C ON E.name = C.name AND E.string_comment = C.string_comment
    JOIN D ON E.name = D.name AND E.string_comment = D.string_comment

Table A,B,C,D are temporary tables and contains 1096 rows and Table E (also temporary table) contains 426 rows. Without creating any index, MySQL EXPLAIN was showing me all the rows being searched from all the Tables. Now, I created a FULLTEXT index for name as name_idx and string_comment as string_idx on all the tables A,B,C,B and E. The EXPLAIN command is still giving me the same result as shown below. Also, please note that name and string_comment are of type VARCHAR and idX are of type int(15)

    id  select_type table  type  possible_keys         key  key_len  ref rows  Extra
    1   SIMPLE      A      ALL   name_idx,string_idx                     1096
    1   SIMPLE      B      ALL   name_idx,string_idx                     1096  Using where
    1   SIMPLE      C      ALL   name_idx,string_idx                     1096  Using where
    1   SIMPLE      D      ALL   name_idx,string_idx                     1096  Using where
    1   SIMPLE      E      ALL   name_idx,string_idx                     426   Using where

Any comments on how can I tune this query?

Thanks.

Luis · Accepted Answer · 2012-01-21 02:26:48Z

1

For each table you should create a composite index on both columns. The syntax varies a bit, but it is something like:

CREATE INDEX comp_E_idx E(name, string_comment)

And repeat for all tables. Separate indices won't help because when it tries to merge they are useless. It searches for the name in the index really fast, but then has to iterate to find the comment

answered Jan 21, 2012 at 2:26

Luis

1,2947 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephen Quan Over a year ago

You should also call ANALYZE TABLE this will further help tune the queries.

Stephen Quan · Accepted Answer · 2012-01-21 12:16:17Z

0

You're asking if the query can be tune. I would first ask if the data, itself can be tuned. i.e. Please consider putting the data in A, B, C, D and E into a single table with NULL-able columns. This will reduce the complexity of searches from O(N^5) to O(N).

answered Jan 21, 2012 at 12:16

Stephen Quan

28k4 gold badges101 silver badges94 bronze badges

Collectives™ on Stack Overflow

How can I optimize MySQL query with multiple joins?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related