Details
-
Bug
-
Status: In Progress (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 11.0
-
None
Description
This is coming from MDEV-20774.
igor wrote:
If there is no range/ref condition useful for an index then it's really impossible to force the server to use full index scan over the index instead of the table scan. And this doesn't comply with description of the FORCE INDEX clause. Looks like an obvious bug.
A testcase (unlike MDEV-20774, let's not use any geometry or GIS):
--source include/have_sequence.inc
|
create table t1( |
a int, |
b int, |
c varchar(100), |
d varchar(100), |
|
|
index idx_a(a), /* non-covering for the query */ |
index idx_ab(a,b), /* covering for the query */ |
index idx_abc(a,b,c) /* excessively covering for the query */ |
);
|
insert into t1 |
select seq, seq, repeat('a',100),repeat('b',100) from seq_1_to_10000; |
analyze table t1; |
|
|
explain select a,b from t1; |
explain select a,b from t1 force index (idx_a); |
explain select a,b from t1 force index (idx_abc); |
|
|
drop table t1; |
Default plan:
explain select a,b from t1;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t1 index NULL idx_ab 10 NULL 10000 Using index
|
One CAN'T force non-index-only full-index-scan:
explain select a,b from t1 force index (idx_a);
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10000
|
One CAN use a full index scan on a covering index:
explain select a,b from t1 force index (idx_ab);
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t1 index NULL idx_ab 10 NULL 9891 Using index
|
|
|
explain select a,b from t1 force index (idx_abc);
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t1 index NULL idx_abc 113 NULL 10000 Using index
|
So, the question is, should query #2 build a non-index-only full-index plan?
Objections
1. The observed behavior seems how it was for a very long time. (and it is so in current MySQL-8, too)
2. The optimizer does not consider or generate non-index-only full-index-scan plans. Why should FORCE INDEX start to generate them?
3. Documentation doesn't say it
MariaDB documentation doesn't give a definite statement about this.
In MySQL's documentation, there is this:
https://dev.mysql.com/doc/refman/8.3/en/index-hints.html
The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table.
Note this part:
a table scan is used only if there is no way to use one of the named indexes to find rows in the table.
|
Note the *there is no way to use one of the named indexes* part. If we assume that FORCE INDEX can use non-index-only full index scans, then there's always a way to use an index, and this passage is meaningless.
Arguments for this
best_access_path() has this code:
if (s->quick) |
...
|
else |
{
|
/* Estimate cost of reading table. */ |
if (s->table->force_index && !best_key) // index scan |
{
|
type= JT_NEXT;
|
tmp= s->table->file->read_time(s->ref.key, 1, s->records); |
}
|
else // table scan |
{
|
tmp= s->scan_time();
|
type= JT_ALL;
|
added by:
https://github.com/MariaDB/server/commit/3bb249612fe3864f152aa990d4d5f267ac9bb688#
Igor's argument is: look at the read_time() call, it is trying to build a full index scan plan. (It also writes that into optimizer trace).
Response: wait, but it makes no distinction between covering index-only scan cost. Could this be a wrong call? (should be index_only_read_time)
A related commit
commit 33fc8037e0a5c54c69732b3ee0eb7aea41392aed
|
Author: Michael Widenius <monty@mariadb.org>
|
Date: Sat Aug 20 08:22:57 2022 +0300
|
|
|
Fixed some issues with FORCE INDEX
|
|
Added code to support that force index can be used to force an index scan
|
instead of a full table scan. Currently this code is disable but I added
|
a test to verify that things works if the code is ever enabled.
|
This has
#ifdef FORCE_INDEX_SHOULD_FORCE_INDEX_SCAN
|
with some logic. But if I enable it, it still doesn't allow non-index-only full-index-scans.
Attachments
Issue Links
- relates to
-
-
- Closed
-