List:General Discussion« Previous MessageNext Message »
From:Akshay Suryavanshi Date:July 11 2012 12:39am
Subject:Re: why does "select * from table oder by indexed_field" not use key?
View as plain text  
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
>
>

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