Hello all,
Default optimizer behavior has changed in 4.0.16 (since 4.0.14)
for this simple question:
select state_id, orderdata_id from order_delivery where
orderdata_id in
(3193340,3193343,3193346,3193349,3193352,3193355)
and is_deleted=0 order by xtime desc
CREATE TABLE `order_delivery` (
`orderdata_id` int(11) NOT NULL default '0',
`state_id` int(11) NOT NULL default '0',
`xtime` datetime NOT NULL default '0000-00-00 00:00:00',
`admin_user_id` int(11) NOT NULL default '0',
`note` text NOT NULL,
`is_deleted` int(1) NOT NULL default '0',
KEY `orderdata_id` (`orderdata_id`),
KEY `is_deleted` (`is_deleted`),
KEY `xtime` (`xtime`)
) TYPE=InnoDB |
4.0.14
=======
mysql> EXPLAIN select state_id, orderdata_id from order_delivery where
-> orderdata_id in
-> (3193340,3193343,3193346,3193349,3193352,3193355)
-> and is_deleted=0 order by xtime desc;
+----------------+-------+-------------------------+--------------+---------+------+------+-----------------------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----------------+-------+-------------------------+--------------+---------+------+------+-----------------------------+
| order_delivery | range | orderdata_id,is_deleted | orderdata_id | 4 | NULL |
5 | Using where; Using filesort |
+----------------+-------+-------------------------+--------------+---------+------+------+-----------------------------+
1 row in set (0.25 sec
4.0.16
=======
+----------------+------+-------------------------+------------+---------+-------+--------+-----------------------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----------------+------+-------------------------+------------+---------+-------+--------+-----------------------------+
| order_delivery | ref | orderdata_id,is_deleted | is_deleted | 4 | const |
228021 | Using where; Using filesort |
+----------------+------+-------------------------+------------+---------+-------+--------+-----------------------------+
1 row in set (0.15 sec)
Due to this simple queries with WHERE IN() became aprox. 30 time slower in my case.
MyISAM in not affected.
Have anyone ever seen such problems with InnoDB tables in 4.0.16?
--
Best regards,
Sergey S. Kostyliov <[EMAIL PROTECTED]>
Public PGP key: http://sysadminday.org.ru/rathamahata.asc
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
