CODEWITHSUNDEEP
mysqlsqldatabaseoptimizationquery-optimization
ppoudel
ppoudel

Reputation: 41

How can I optimize MySQL query with multiple joins?

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.

Upvotes: 1

Views: 1408

Answers (2)

Stephen Quan
Stephen Quan

Reputation: 26334

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

Upvotes: 0

Luis
Luis

Reputation: 1294

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

Upvotes: 1

Related Questions