On Jun 15, 2010, at 3:57 PM, Zardosht Kasheff wrote:
> Hello all,
> I have a MyISAM table foo with the following schema:
> (a int, b int, c int, d int, ....)
> and with keys:
> The query is :
> select c, count(*) num_cnt, from foo where a > 1000 and b is not NULL,
> group by c, order by num_cnt;
> Note that both keys are covering indexes.
> When I run the query, MySQL properly selects the key (a,b,c). However,
> when I add a "limit 10" to the end, the key (c,b,d,a) is used. Using
> (c,b,d,a) is not good because the entire index will need to be read to
> find the 10 values of 'c' that show up the most, whereas if (a,b,c) is
> used, only a range is done (where a > 1000).
> When I run this with MyISAM, the proper query plan returns in 1
> second, whereas the bad one returns in 6 seconds.
> Does anyone have insight into what may be going on?
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: http://lists.mysql.com/internals?unsub=1