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.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 > >
