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
signature.asc
Description: OpenPGP digital signature
