Bug #8569 mysql ignores multi-colun index even, on force, max_seeks_for_key=1
Submitted: 17 Feb 2005 15:06 Modified: 28 Feb 2005 17:11
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.10 OS:Any (*)
Assigned to: Timour Katchaounov CPU Architecture:Any

[17 Feb 2005 15:06] Martin Friebe
Description:
mysql ignores the 2nd (and consecutive) parts of multi column indexes.

This happens if the first part of the index is used in the ON condition of a join, and the 2nd part is a range in the where.

See how to repeat.

Neither force index (which according to the doc, should use the index at any cost), nor max_seeks_for_key=1 did convince the server to use the full index.
Only if the where part is an equal condition, mysql uses the full index 

See also the later example with a range matching only one integer (between 2.9 and 3.1 or with impossible where, do not use the index.

looking on the amount of rows examined in the first table, and the amount of indexpages, that would possible have to be loaded, this decission makes no sense. Especialy since mysql is allready using the index, it should use the index for the where, even if this is a larger range. (I have similiar experience on tables far bigger, having to load 50000 rows for a where that could be satisfied by an allready used index, is very expensive (and I doubt that there would be as many key-page to be loaded)

How to repeat:

create table tb1 (a int, b int, c varchar(5));
create table tb2 (x int, y int, z varchar(5));
alter table tb2 add index i1 (x,y);

insert into tb1 values (1,2,'foo'),  (2,3,'foo'), (3,2,'foo'), (4,4,'foo'), (5,2,'foo'), (6,5,'foo'), (10,2,'foo'),  (12,3,'foo'), (13,2,'foo'), (14,4,'foo'), (15,2,'foo'), (16,5,'foo');
insert into tb2 values (1,2,'foo'),  (2,3,'foo'), (3,2,'foo'), (4,4,'foo'), (5,2,'foo'), (6,5,'foo'), (10,2,'foo'),  (12,3,'foo'), (13,2,'foo'), (14,4,'foo'), (15,2,'foo'), (16,5,'foo');

analyze table tb1;
analyze table tb2;

# normal join, index is used as expected
 explain select * from tb1 left join tb2 on a= x ;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
|  1 | SIMPLE      | tb1   | ALL  | NULL          | NULL |    NULL | NULL      |   12 |       |
|  1 | SIMPLE      | tb2   | ref  | i1            | i1   |       5 | xxx.tb1.a |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
2 rows in set (0.00 sec)

# equal in the where condition, full index (length 10) is used
mysql>  explain select * from tb1 left join tb2 on a= x where y=3;
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref             | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | tb1   | ALL  | NULL          | NULL |    NULL | NULL            |   12 |             |
|  1 | SIMPLE      | tb2   | ref  | i1            | i1   |      10 | xxx.tb1.a,const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
2 rows in set (0.01 sec)

#several examples with a range in where, index is only used with length of 5
mysql>  explain select * from tb1 left join tb2 on a= x where y>2 and y<5;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
|  1 | SIMPLE      | tb1   | ALL  | NULL          | NULL |    NULL | NULL      |   12 |             |
|  1 | SIMPLE      | tb2   | ref  | i1            | i1   |       5 | xxx.tb1.a |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

# force index, still only using first 5 bytes
mysql>  explain select * from tb1 left join tb2 force index (i1) on a= x where y>2 and y<5;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
|  1 | SIMPLE      | tb1   | ALL  | NULL          | NULL |    NULL | NULL      |   12 |             |
|  1 | SIMPLE      | tb2   | ref  | i1            | i1   |       5 | xxx.tb1.a |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

# should be acting like force any index
set max_seeks_for_key=1;

explain select * from tb1 left join tb2 force index (i1) on a= x where y>2 and y<5;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
|  1 | SIMPLE      | tb1   | ALL  | NULL          | NULL |    NULL | NULL      |   12 |             |
|  1 | SIMPLE      | tb2   | ref  | i1            | i1   |       5 | xxx.tb1.a |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+

alter table tb1 add unique (a); # doesnt matter, 

explain select * from tb1 left join tb2 force index (i1) on a= x where y>2 and y<5;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
|  1 | SIMPLE      | tb1   | ALL  | a             | NULL |    NULL | NULL      |   12 |             |
|  1 | SIMPLE      | tb2   | ref  | i1            | i1   |       5 | xxx.tb1.a |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

# this range (on an integer) is allowing only y=3, but still

explain select * from tb1 left join tb2 force index (i1) on a= x where y>2.9 and y<3.1;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
|  1 | SIMPLE      | tb1   | ALL  | a             | NULL |    NULL | NULL      |   12 |             |
|  1 | SIMPLE      | tb2   | ref  | i1            | i1   |       5 | xxx.tb1.a |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
2 rows in set (0.01 sec)

# impossible where (on integer) is not regocnized

explain select * from tb1 left join tb2 force index (i1) on a= x where y>2 and y<3;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
|  1 | SIMPLE      | tb1   | ALL  | a             | NULL |    NULL | NULL      |   12 |             |
|  1 | SIMPLE      | tb2   | ref  | i1            | i1   |       5 | xxx.tb1.a |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

 show index from tb2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tb2   |          1 | i1       |            1 | x           | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |
| tb2   |          1 | i1       |            2 | y           | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.02 sec)

Suggested fix:
ensure that the full index if used, if the optimizer decides to use the first part of the index, and other conditions to the 2nd part of the index exist.

ensure force index works on the full index (not only its first part)
[17 Feb 2005 18:38] Martin Friebe
Just to say, in the example, there is only one matching row in the right table for each in the left. however, it does also happen, if you double all the values in the 2nd table. So it does not depend on this "special case"
[28 Feb 2005 17:11] Timour Katchaounov
This is not a bug, this is the way the system currently works (both 4.1 and 5.0), and this
behavior is documented.

Generally there are three alternatives for queries with comparison operators:
1)
Use the "range access method". In this case this is not possible, because as
Section "7.2.5.2. Range Access Method for Multiple-Part Indexes" says:
" For a BTREE index, an interval might be usable for conditions combined with AND,
where each condition compares a key part with a constant value using =, <=>, IS NULL,
>, <, >=, <=, !=, <>, BETWEEN, or LIKE 'pattern' (where 'pattern' doesn't start with
a wildcard)."
Notice the word "constant". In the example queries the equality compares values of two
table attributes, not one attribute with a constant, so this method is not applicable.
2)
Use the "ref" access method. As Section "7.2.1. EXPLAIN Syntax (Get Information About
a SELECT)" says:
"ref can be used for indexed columns that are compared using the =  operator."
Well, in this case we have inequalities, so the ref access method uses only the
keyparts referenced in the equality, in this case only the first key part.
3)
Use "range checked for each record". As documented, this method will be used only
if better than a full table scan, which is not the case with our small example.

The combination of 1) and 2) is a rather big new feature that will be implemented
in some future version of MySQL.
[25 Oct 2023 14:40] Hatim Ben Abdelmaoula
Hi,
As mentioned by Timour Katchaounov, there will be a new feature to fix/improve this behaviour in order to use the full index. 
Is there any update regarding this matter?