[#MDEV-7447] Optimizer choose different index depends on scan rows in ORDER BY DESC

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?

[MDEV-7447] Optimizer choose different index depends on scan rows in ORDER BY DESC Created: 2015-01-12 Updated: 2021-12-02
Status:	Open
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.0.15, 10.4.21
Fix Version/s:	10.4

[MDEV-7447] Optimizer choose different index depends on scan rows in ORDER BY DESC Created: 2015-01-12 Updated: 2021-12-02