List:General Discussion« Previous MessageNext Message »
From:Sergey S. Kostyliov Date:October 29 2003 9:15am
Subject:MySQL/InnoDB-4.0.16 +Optimizer behaviour
View as plain text  
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 <rathamahata@stripped>
                   Public PGP key: http://sysadminday.org.ru/rathamahata.asc

Thread
MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri22 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan22 Oct
  • 2 Query with same criteria giving different number of rowsHector Del Curto22 Oct
    • Re: 2 Query with same criteria giving different number of rowsChris Tucker22 Oct
    • Re: 2 Query with same criteria giving different number of rowsIllyes Laszlo23 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam23 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam23 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies24 Oct
    • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam24 Oct
    • MySQL/InnoDB-4.0.16 +Optimizer behaviourSergey S. Kostyliov29 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri27 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri26 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies26 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Jon Hancock27 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan27 Oct
    • InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)Gabriel Ricard27 Oct
  • Partial replicate InnoDB -> MyISAMJon Hancock28 Oct
    • Re: Partial replicate InnoDB -> MyISAMChris Nolan28 Oct
  • Re: Partial replicate InnoDB -> MyISAMJon Hancock28 Oct
Re: InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)Heikki Tuuri27 Oct