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.rei...@thelounge.net
> <mailto:h.rei...@thelounge.net>> 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
signature.asc
Description: OpenPGP digital signature
