Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.15, 10.4.21
-
None
Description
I am not sure if this is related to MDEV-6657. Here is the basic table information, note that this is an archived table.
size: 110G
number of rows: 700 million
there are two secondary indexes/columns involving in this issue, one is userid ( INT ), one is access ( datetime )
example query:
SELECT * FROM mytable WHERE userid = 123456 ORDER BY access LIMIT 1;
|
So if the scan rows are little, optimizer will just use 'userid' index to retrieve the rows.
+------+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+
|
| 1 | SIMPLE | mytable | ref | userid | userid | 4 | const | 49 | Using where; Using filesort |
|
+------+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+
|
However, if we hit some userids that have more historical data, then optimizer intends to use 'access' index to help in sorting and retrieving rows in the same time if my understanding is correct.
+------+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|
| 1 | SIMPLE | mytable | index | userid | access | 8 | NULL | 24616 | Using where |
|
+------+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|
Apparently, the second execution plan runs forever, > 1 hour, but the first execution plan runs < 7 secs.
I have to 'FORCE INDEX (userid)' to make sure a stable execution plan, but maybe there is more thing we can consider in this kind of optimization like checking the size of 'access' index before using it or something like that?
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- relates to
-
MDEV-18079 Opportunistic optimization for ORDER BY LIMIT N queries
-
- Open
-