Hi,
On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald <h.rei...@thelounge.net> wrote:
> the mysql query optimizer is somehow stupid
Its not stupid - remember its not trying to find the best index,
rather its trying to find the least costly plan
to return the data in the quickest manner.
For the optimizer in this case it believes its faster to do a full
table scan with filesort rather than read from the index
and have to scan the entire table anyway.
Quick test shows it is indeed faster to do a full table scan.
mysql> show profiles;
+----------+------------+------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query
|
+----------+------------+------------------------------------------------------------------------------------------+
| 1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
| 2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
cms1_quickbar_groups ORDER BY qg_sort ASC |
+----------+------------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Cheers,
Ewen
>
> 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
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
