List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:July 11 2012 10:42am
Subject:Re: why does "select * from table oder by indexed_field" not use key?
View as plain text  
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.reindl@stripped>wrote:

>
>
> Am 11.07.2012 11:43, schrieb Ewen Fortune:
> > Hi,
> >
> > On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald <h.reindl@stripped>
> 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
>
>

Thread
why does "select * from table oder by indexed_field" not use key?Reindl Harald10 Jul
  • Re: why does "select * from table oder by indexed_field" not use key?Akshay Suryavanshi11 Jul
    • Re: why does "select * from table oder by indexed_field" not usekey?Reindl Harald11 Jul
      • Re: why does "select * from table oder by indexed_field" not use key?Ewen Fortune11 Jul
        • Re: why does "select * from table oder by indexed_field" not usekey?Reindl Harald11 Jul
          • Re: why does "select * from table oder by indexed_field" not use key?Ananda Kumar11 Jul
          • Re: why does "select * from table oder by indexed_field" not use key?Stephen Tu11 Jul