MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize Query with multiple joins
Posted by: Rick James
Date: March 28, 2011 11:05PM

The dataset is quite small. The total size of all the indexes (that you have shown) is less than key_buffer_size, so there is probably no I/O for reading indexes once everything gets cached.

Rows_examined = 39
EXPLAIN "rows": 11,1,1,1,1,1,1,1
These are reasonably consistent. And the numbers are very small. Today I was looking at a 2-table join with Rows_examined = 4 million, and query_time consistently ~11 seconds.

1.43 seconds (as you say) is unreasonable.

Let's say the "rows" have to be read from disk, but the indexes are cached. Then there would be about 39 disk reads, which should take about 0.39 sec., still << 1.43 s.

So what else could it be?
* Other programs hogging the CPU or disk.
* Other MySQL threads similarly hogging resources.

What can be done? Well, it chose to start with t_1, which may not be optimal. Since you have not provided even what the PRIMARY KEY is, I will throw some guesses of optimal indexes in:

    WHERE  dest.r=1
      and  dest.i=1
      and  dest.min IN (1,2,3,4,5,6,7,8)
      and  dest.max >=21
INDEX(r, i, min, max)
      and  dc.destId_A=3000
      and  dc.value>=35
      and  dc.points<=10500
INDEX(destId_A, value) or
INDEX(destId_a, points)
      and  act_r.actId=21
      and  act_r.value >= 30
INDEX(actId, value)
      and  a_0.actId=2
      and  a_0.value >= 20
INDEX(actId, value)
      and  t_0.act_t= 4
      and  t_0.value_average >= 40 //...
INDEX(act_t, value_average)
Perhaps one of those will decrease Rows_examined down from 39.

Another thing to strive for is "Using index" (see EXPLAIN). But I don't have enough details to give specifics.

Oh, one more thing:
SHOW VARIABLES LIKE 'query%';
SHOW GLOBAL STATUS LIKE 'Qc%';
It could be that your Query cache is too big, and a write came along an purged things from the QC. This can cause sporadic, big delays.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize Query with multiple joins
1875
March 28, 2011 11:05PM


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.