MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize Query with multiple joins
Posted by: Rick James
Date: March 25, 2011 07:46AM

Jorgen's comments are fine based on info info you gave.
First, let's make your query a bit more readable...
SELECT  distinct d.id, //several fields
    from  several tables
    FROM  dest
    INNER JOIN  act_r ON act_r.destId=dest.id
    INNER JOIN  dest_c dc ON dc.destId_B=dest.id
    INNER JOIN  act_r a_0 ON dest.id=a_0.destId
    INNER JOIN  act_r a_1 ON dest.id=a_1.destId
    INNER JOIN  act_t t_0 ON dest.id=t_0.destId
    INNER JOIN  act_t t_1 ON dest.id=t_1.destId
    INNER JOIN  act_t t_2 ON dest.id=t_2.destId
    WHERE  dest.r=1
      and  dest.i=1
      and  dest.min IN (1,2,3,4,5,6,7,8)
      and  dest.max >=21
      and  dc.destId_A=3000
      and  dc.value>=35
      and  dc.points<=10500
      and  act_r.actId=21
      and  act_r.value >= 30
      and  a_0.actId=2
      and  a_0.value >= 20
      and  t_0.act_t= 4
      and  t_0.value_average >= 40 //similar conditions for the rest of the tables join ed
    limit  40;

What are the indexes?
Which Engine are you using?
Is this an EAV schema?

Since most of the JOINs are tied on the same id (destId), this sounds more like "Vertical Partitioning" than "Normalization". Unless you have some big columns (BLOB/TEXT), Vertical Partitioning will cost, not save.

The ORDER BY may lead to _terrible_ performance. Try it, and get the EXPLAIN plan for it. Without the ORDER BY, the query can start with any of the tables, and probably stop after not many rows. With the ORDER BY, and because of the numerous JOINs, it may have to collect _all_ the possible result rows, then sort, and finally deliver what LIMIT says.

No, I have not addressed your question about slowing down due to load. Again, I need more info.

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

If you get squeezed down to 2GB (which is _tiny_ these days), the cache sizes and the table sizes (in bytes, not just rows) become important numbers to see and discuss.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize Query with multiple joins
2471
March 25, 2011 07:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.