MySQL Bugs: #4478: Wrong use of indices for InnoDB tables

Bug #4478	Wrong use of indices for InnoDB tables
Submitted:	8 Jul 2004 23:28	Modified:	1 Nov 2004 23:16
Reporter:	Marc Bouffard	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.0.18	OS:	Linux (Redhat Kernel 2.4.20)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
A)

explain SELECT count(watchpoint_id) FROM wp_summary_object_tb FORCE INDEX (start_time) WHERE start_time >= 1088171135 AND start_time < 1088740800;
+----------------------+-------+---------------+------------+---------+------+-------+-------------+
| table                | type  | possible_keys | key        | key_len | ref  | rows  | Extra       |
+----------------------+-------+---------------+------------+---------+------+-------+-------------+
| wp_summary_object_tb | range | start_time    | start_time |       4 | NULL | 10332 | Using where |
+----------------------+-------+---------------+------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

B)

explain SELECT count(watchpoint_id) FROM wp_summary_object_tb WHERE start_time >= 1088171135 AND start_time < 1088740800;
+----------------------+------+---------------+------+---------+------+-------+-------------+
| table                | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----------------------+------+---------------+------+---------+------+-------+-------------+
| wp_summary_object_tb | ALL  | start_time    | NULL |    NULL | NULL | 31971 | Using where |
+----------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

Why doesn't the "B" query use the start_time index?  If I force the index, it shows a query type of "range" which is far superior to "All" (according to the MySQL docs, it should choose range over ALL as the better type).  The where clause is identical (the only difference is the FORCE INDEX).  So why doesn;t the optimizer select the right index for query B.

Index info:

+----------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| wp_summary_object_tb |          0 | PRIMARY        |            1 | id             | A         |       31971 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          1 | start_time     |            1 | start_time     | A         |        3552 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          1 | end_time       |            1 | end_time       | A         |        3197 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          1 | total_requests |            1 | total_requests | A         |       31971 |     NULL | NULL   |      | BTREE      |         |
+----------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.02 sec)

How to repeat:
Need to define an InnoDB table, and have a certain amount of records in it (not sure how many - but this problem does not happen on tables with fewer number of records), then do a simple select with a where clause that uses the indices.

Ya, I know that sucks as a "how to reproduce", but I've been looking at this all day and still don't have any clue as to why this is happening.

Hi!

In theory, accessing 10 000 rows through a secondary index might require 10 000 random reads from the disk. That would be much slower than scanning the table sequentially.

In your case, random reads probably will not happen, because the table is so small that it fits completely in the buffer pool. But the MySQL optimizer currently does not take into account the size of the table, and if the table fits completely in the buffer pool.

Regards,

Heikki

I am pretty sure the size of the table is taken into account Heikki, in the same database structure, but with a smaller record set, I get this:

A) 
mysql> explain SELECT count(watchpoint_id) FROM wp_summary_object_tb FORCE INDEX (start_time) WHERE start_time >= 1088171135 AND start_time < 1088740800;
+----------------------+-------+---------------+------------+---------+------+------+-------------+
| table                | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----------------------+-------+---------------+------------+---------+------+------+-------------+
| wp_summary_object_tb | range | start_time    | start_time |       4 | NULL |   10 | Using where |
+----------------------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

b) explain SELECT count(watchpoint_id) FROM wp_summary_object_tb WHERE start_time >= 1088171135 AND start_time < 1088740800 AND 1>0;
+----------------------+-------+---------------+------------+---------+------+------+-------------+
| table                | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----------------------+-------+---------------+------------+---------+------+------+-------------+
| wp_summary_object_tb | range | start_time    | start_time |       4 | NULL |   10 | Using where |
+----------------------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Index info:

+----------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| wp_summary_object_tb |          0 | PRIMARY        |            1 | id             | A         |          53 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          0 | watchpoint_id  |            1 | watchpoint_id  | A         |           7 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          0 | watchpoint_id  |            2 | start_time     | A         |          53 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          1 | start_time     |            1 | start_time     | A         |          53 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          1 | end_time       |            1 | end_time       | A         |          53 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          1 | total_requests |            1 | total_requests | A         |          53 |     NULL | NULL   |      | BTREE      |         |
+----------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)

So with much fewer rows it has no problem figuring out to use the index.  In this case with so few rows in the result set, I agree that it makes no difference.  You'd imagine if it succesfully makes the right decidion for a small result set, it would have made the right index decidion for a larger result set...but apparently not.  The only factor that I can see that causes the right index to be chosen is, in fact, the size of the table.  If "the table is so small it fits in the buffer pool", then the second example should have the same problem.

Noticed that the index is not exactly the same in my last example, so I dropped the offensive index in case it might have influenced the index choice.  The results are the same:

a) explain SELECT count(watchpoint_id) FROM wp_summary_object_tb FORCE INDEX (start_time) WHERE start_time >= 1088171135 AND start_time < 1088740800 AND 1>0;
+----------------------+-------+---------------+------------+---------+------+------+-------------+
| table                | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----------------------+-------+---------------+------------+---------+------+------+-------------+
| wp_summary_object_tb | range | start_time    | start_time |       4 | NULL |   10 | Using where |
+----------------------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

b)  explain SELECT count(watchpoint_id) FROM wp_summary_object_tb WHERE start_time >= 1088171135 AND start_time < 1088740800;
+----------------------+-------+---------------+------------+---------+------+------+-------------+
| table                | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----------------------+-------+---------------+------------+---------+------+------+-------------+
| wp_summary_object_tb | range | start_time    | start_time |       4 | NULL |   10 | Using where |
+----------------------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Index info:

show index from wp_summary_object_tb;
+----------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| wp_summary_object_tb |          0 | PRIMARY        |            1 | id             | A         |          62 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          1 | start_time     |            1 | start_time     | A         |          62 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          1 | end_time       |            1 | end_time       | A         |          62 |     NULL | NULL   |      | BTREE      |         |
| wp_summary_object_tb |          1 | total_requests |            1 | total_requests | A         |          62 |     NULL | NULL   |      | BTREE      |         |
+----------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

Any news?

Anyone?  Bueller?

Hi,

Thank you for your bug report!  Could you provide me with the show create table
output for this table?  If the data is not sensitive a dump of the table would also
be helpful for me to try and repeat this in 4.0.21 using innodb and myisam tables.

There is usually a good reason for the optimizer's choices regarding indexes and I'll
need the table structure and at least some dummy rows to show that this is or is not
a bug.

What are the actual execution times when using force index and not?

Also a small note, the last statement you show with the smaller table has " and 1>0" appended to 
it and this may change the results.

Best Regards

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".