Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL), 10.2(EOL), 10.8(EOL)
-
None
-
None
Description
(The idea of this was given by igor, for descending indexes. Then, I modified the example so it applies to ascending indexes)
Range optimizer creates a range that's wider than necessary for conditions in this and similar forms:
CREATE INDEX idx(kp1, kp2) ON ...; |
 |
SELECT ... FROM t WHERE kp1 >= const1 AND kp2 < const2; |
The range is:
const1 <= kp1
|
while one could use a narrower collection o franges:
( kp1=const1 AND kp2 < const2) OR |
(kp1>const)
|
A testcase:
create table t1 ( |
kp1 int not null, |
kp2 int not null, |
key kp(kp1, kp2) |
);
|
insert into t1 select A.seq, B.seq from seq_1_to_100 A, seq_1_to_100 B; |
set optimizer_trace=1; |
explain select * from t1 force index(kp) where kp1>=10 and kp2 <50; |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | range | kp | kp | 4 | NULL | 5000 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
Note key_len=4, it uses one key part
Optimizer trace confirms this:
select json_detailed(json_extract(trace,'$**.range_scan_alternatives))
|
from information_schema.optimizer_trace\G
|
json_detailed(json_extract(trace,'$**.range_scan_alternatives')): [
|
[
|
{
|
"index": "kp",
|
"ranges":
|
[
|
"(10) <= (kp1)"
|
],
|
...
|