List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:July 11 2012 9:46am
Subject:Re: why does "select * from table oder by indexed_field" not use
key?
View as plain text  

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


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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