MySQL Forums
Forum List  »  Optimizer & Parser

Re: Execution plan: wrong index selected, very poor performance.
Posted by: Andrej Andrejevic
Date: July 20, 2012 01:38AM

Rick James Wrote:
-------------------------------------------------------
> What percent of the table is that month? This can
> make a difference both in whether INDEX(c1) is
> chosen, and whether it is a 'good' choice. If
> most of the rows are in that month, the the index
> actually hurts performance -- it has to bounce
> between the index and the data.

I've tested with different ranges and noticed the magic number is somewhere between 8 and 9M rows. If it's less it uses current index. Same thing happens if I drop group and order by and just do a simple select. It's a full table scan vs using proper index.

>
> Did you try the 'compound' index
> INDEX(c1, c2, c10)
> Then the entire query could be performed in the
> index.

Not yet, building it now, will report back.

> Note the "767", then...
> * Don't use VARCHAR(255) if the strings will
> always be short.
> * Don't use utf8 if you know all the data will be
> ascii.
> * "102" implies that 255 is serious overkill.
>
> Always have an explicit PRIMARY KEY for InnoDB
> tables, even if it is an artificial INT UNSIGNED
> NOT NULL.
>

Thanks for the heads up.

> How much RAM do you have? Assign 70% of
> _available_ RAM to innodb_buffer_pool_size.
>

7.5GB, 75% assigned to buffer_pool_size.

> ALTER TABLE foo ORDER BY c1, c2, c10;
>
> Are the values of those INTs (4 bytes each) small
> enough to fit into MEDIUMINT/SMALLINT/TINYINT
> (3/2/1 bytes). Smaller --> more cacheable -->
> faster.

Will try and report back, takes a while.

> > Why's it using key_c2 instead of key_c1?
> * Using c1 avoids looking at all the data.
> * Using c2 avoids a sort (one sort for both the
> GROUP BY and ORDER BY).
> The optimizer has no practical way to decide which
> is better. Hence, it sometimes picks the wrong
> one.

Thanks for the amazing help.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Execution plan: wrong index selected, very poor performance.
1565
July 20, 2012 01:38AM


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.