List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:July 11 2012 8:31am
Subject:Re: why does "select * from table oder by indexed_field" not use
key?
View as plain text  
the mysql query optimizer is somehow stupid

a simple query, order by with a indexed column and
you have to use where order_by_field>0 - why the
hell is mysqld not happy that a key is on the field
used in "order by"?

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.00 sec)

mysql> EXPLAIN SELECT * FROM cms1_quickbar_groups where qg_sort>0 ORDER BY qg_sort
> ASC;
+----+-------------+----------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table                | type  | possible_keys | key     | key_len |
ref  | rows | Extra       |
+----+-------------+----------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | cms1_quickbar_groups | range | qbq_key       | qbq_key | 2       |
NULL |    2 | Using where |
+----+-------------+----------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


Am 11.07.2012 02:39, schrieb Akshay Suryavanshi:
> 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
> <mailto: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
> 
> 

-- 

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm




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