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)