Re: Execution plan: wrong index selected, very poor performance.
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.