From: Akshay Suryavanshi
Date: July 11 2012 12:39am
Subject: Re: why does "select * from table oder by indexed_field" not use key?
List-Archive: http://lists.mysql.com/mysql/227798
Message-Id: <CAL9M2p02AWwdsJyYbduJUVZLxU3UJ5-Hcbc-7F-Q8L1Kh9CVUA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=14dae9340e299f671004c48313d7

--14dae9340e299f671004c48313d7
Content-Type: text/plain; charset=ISO-8859-1

Hi,

The statement will do a Full table scan, because of the following things :
Not using "Where" clause, and selecting "all columns (*)" within the query.
Filesort is used since no index is used, use a where clause with condition
on column which is indexed and notice the explain plan. Also you can
retrieve specific columns on which indexes are created to use the feature
of "Covering index".

On Wed, Jul 11, 2012 at 3:19 AM, Reindl Harald <h.reindl@stripped>wrote:

> my reason for create a key on "qg_sort" was primary
> for this query - but why is here 'filesort' used?
>
> mysql> EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
>
> +----+-------------+----------------------+------+---------------+------+---------+------+------+----------------+
> | id | select_type | table                | type | possible_keys | key  |
> key_len | ref  | rows | Extra          |
>
> +----+-------------+----------------------+------+---------------+------+---------+------+------+----------------+
> |  1 | SIMPLE      | cms1_quickbar_groups | ALL  | NULL          | NULL |
> NULL    | NULL |    2 | Using filesort |
>
> +----+-------------+----------------------+------+---------------+------+---------+------+------+----------------+
> 1 row in set (0.01 sec)
> -
> cms1_quickbar_groups | CREATE TABLE `cms1_quickbar_groups` (
>   `qg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
>   `qg_titel` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
>   `qg_titel_en` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
>   `qg_sort` smallint(3) unsigned NOT NULL DEFAULT '0',
>   PRIMARY KEY (`qg_id`),
>   KEY `qbq_key` (`qg_sort`)
> ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
> COLLATE=latin1_german1_ci PACK_KEYS=1 DELAY_KEY_WRITE=1
>
>

--14dae9340e299f671004c48313d7--