From: Ananda Kumar Date: July 11 2012 10:42am Subject: Re: why does "select * from table oder by indexed_field" not use key? List-Archive: http://lists.mysql.com/mysql/227802 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=f46d042ef48b0b0ec404c48b82b9 --f46d042ef48b0b0ec404c48b82b9 Content-Type: text/plain; charset=ISO-8859-1 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 wrote: > > > Am 11.07.2012 11:43, schrieb Ewen Fortune: > > Hi, > > > > On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald > 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 > > --f46d042ef48b0b0ec404c48b82b9--