Bug #1973 TPC-C query does not uses indexes efficently

• Submitted: 27 Nov 2003 9:16
• Modified: 13 Dec 2003 18:57
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Won't fix
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 4.0
• OS: Any (all)
• Assigned to: Igor Babaev
• CPU Architecture: Any

[27 Nov 2003 9:16] Peter Zaitsev

Description:
The following query, one of the queries in TPC-C benchmark is not optimized well,
the latest keypart for some reason is not used for primary key of order_line table:

mysql> explain SELECT count(DISTINCT s_i_id) FROM order_line, stock, district        WHERE d_id = 10 AND d_w_id = 1 AND d_id = ol_d_id        AND d_w_id = ol_w_id AND ol_i_id = s_i_id        AND ol_w_id = s_w_id AND s_quantity < 17        AND ol_o_id BETWEEN (d_next_o_id - 20) AND (d_next_o_id - 1);
+------------+------+---------------+---------+---------+--------------------------+-------+-------------+
| table      | type | possible_keys | key     | key_len | ref                      | rows  | Extra       |
+------------+------+---------------+---------+---------+--------------------------+-------+-------------+
| district   | ref  | d_idx         | d_idx   |      10 | const,const              |     1 | Using where |
| order_line | ref  | PRIMARY       | PRIMARY |       8 | const,const              | 96924 | Using where |
| stock      | ref  | s_idx         | s_idx   |      10 | order_line.ol_i_id,const |     1 | Using where |
+------------+------+---------------+---------+---------+--------------------------+-------+-------------+
3 rows in set (0.00 sec)

CREATE TABLE `order_line` (
  `ol_w_id` int(11) NOT NULL default '0',
  `ol_d_id` int(11) NOT NULL default '0',
  `ol_o_id` int(11) NOT NULL default '0',
  `ol_number` int(11) NOT NULL default '0',
  `ol_i_id` int(11) default NULL,
  `ol_delivery_d` date default NULL,
  `ol_amount` int(6) default NULL,
  `ol_supply_w_id` int(11) default NULL,
  `ol_quantity` int(11) default NULL,
  `ol_dist_info` char(24) default NULL,
  PRIMARY KEY  (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`)
) TYPE=InnoDB
CREATE TABLE `stock` (
  `s_i_id` int(11) default NULL,
  `s_w_id` int(11) default NULL,
  `s_quantity` int(11) default NULL,
  `s_ytd` int(11) default NULL,
  `s_order_cnt` int(11) default NULL,
  `s_remote_cnt` int(11) default NULL,
  `s_data` varchar(50) default NULL,
  `s_dist_01` varchar(24) default NULL,
  `s_dist_02` varchar(24) default NULL,
  `s_dist_03` varchar(24) default NULL,
  `s_dist_04` varchar(24) default NULL,
  `s_dist_05` varchar(24) default NULL,
  `s_dist_06` varchar(24) default NULL,
  `s_dist_07` varchar(24) default NULL,
  `s_dist_08` varchar(24) default NULL,
  `s_dist_09` varchar(24) default NULL,
  `s_dist_10` varchar(24) default NULL,
  KEY `s_idx` (`s_i_id`,`s_w_id`)
) TYPE=InnoDB

CREATE TABLE `district` (
  `d_id` int(11) default NULL,
  `d_w_id` int(11) default NULL,
  `d_ytd` int(12) default NULL,
  `d_tax` int(4) default NULL,
  `d_next_o_id` int(11) default NULL,
  `d_name` varchar(10) default NULL,
  `d_street_1` varchar(20) default NULL,
  `d_street_2` varchar(20) default NULL,
  `d_city` varchar(20) default NULL,
  `d_state` char(2) default NULL,
  `d_zip` varchar(9) default NULL,
  KEY `d_idx` (`d_w_id`,`d_id`)
) TYPE=InnoDB

Note: This also happens with MyISAM tables. 

How to repeat:
Download the tables  in "secret directory": brokentpc.sql.gz and try the query.

[13 Dec 2003 18:57] Michael Widenius

There is no way that the current optimizer could use a better index for
order_line.

The 3'd part of the primary key is ol_o_id.  This is used in the WHERE clause as:

ol_o_id BETWEEN (d_next_o_id - 20) AND (d_next_o_id - 1)

MySQL can't use this as a ref key as this is not of type ol_o_id = constant.

To add a special optimization for this is something that is not a bug but a worklog task.