Re: why does "select * from table oder by indexed_field" not use key?

[Reindl Harald]

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