column used in the order by caluse, should be the first column in the select statement to make the index work
On Wed, Jul 11, 2012 at 3:16 PM, Reindl Harald <h.rei...@thelounge.net>wrote: > > > Am 11.07.2012 11:43, schrieb Ewen Fortune: > > Hi, > > > > On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald <h.rei...@thelounge.net> > wrote: > >> the mysql query optimizer is somehow stupid > > > > Its not stupid - remember its not trying to find the best index, > > rather its trying to find the least costly plan > > to return the data in the quickest manner. > > > > For the optimizer in this case it believes its faster to do a full > > table scan with filesort rather than read from the index > > and have to scan the entire table anyway. > > > > Quick test shows it is indeed faster to do a full table scan. > > > > mysql> show profiles; > > > +----------+------------+------------------------------------------------------------------------------------------+ > > | Query_ID | Duration | Query > > | > > > +----------+------------+------------------------------------------------------------------------------------------+ > > | 1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM > > cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC | > > | 2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM > > cms1_quickbar_groups ORDER BY qg_sort ASC | > > > +----------+------------+------------------------------------------------------------------------------------------+ > > 2 rows in set (0.00 sec) > > this may be true for small data where it does not matter at all > but if this would be a large table it would cause a lot of I/O > >
