From: Reindl Harald Date: July 11 2012 8:31am Subject: Re: why does "select * from table oder by indexed_field" not use key? List-Archive: http://lists.mysql.com/mysql/227799 Message-Id: <4FFD39E5.9030607@thelounge.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigD9D9464D53D839A37C3CC33D" --------------enigD9D9464D53D839A37C3CC33D Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 B= Y 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_k= ey | 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 thing= s : 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 retri= eve specific columns on which indexes are > created to use the feature of "Covering index". >=20 > On Wed, Jul 11, 2012 at 3:19 AM, Reindl Harald > wrote: >=20 > my reason for create a key on "qg_sort" was primary > for this query - but why is here 'filesort' used? >=20 > 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 DEFAUL= T '', > `qg_titel_en` varchar(255) COLLATE latin1_german1_ci NOT NULL DEF= AULT '', > `qg_sort` smallint(3) unsigned NOT NULL DEFAULT '0', > PRIMARY KEY (`qg_id`), > KEY `qbq_key` (`qg_sort`) > ) ENGINE=3DMyISAM AUTO_INCREMENT=3D3 DEFAULT CHARSET=3Dlatin1 COLLA= TE=3Dlatin1_german1_ci PACK_KEYS=3D1 DELAY_KEY_WRITE=3D1 >=20 >=20 --=20 Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofm=FChlgasse 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 --------------enigD9D9464D53D839A37C3CC33D Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk/9OeUACgkQhmBjz394AnkwRgCfVbk7RTE2rrCg5W57arapvMsk r6cAmwVVz0W56OY7jg2rtLcs7jJEhBxl =p+jW -----END PGP SIGNATURE----- --------------enigD9D9464D53D839A37C3CC33D--