[MDEV-26881] Selects and Updates with huge fixed set in where clause performs well in 5.5 master, poorly in 10.5 slave Created: 2021-10-21  Updated: 2022-01-06  Resolved: 2021-11-25

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Edward Stoever Assignee: Sergei Petrunia
Resolution: Cannot Reproduce Votes: 1
Labels: None


 Description   

EXPLAIN select * from mytable where userid IN (<fixed set of 10000 integers>);
5.5 EXPLAIN

+------+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
 
| id   | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
 
+------+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
 
|    1 | SIMPLE      | mytable        | range | PRIMARY       | PRIMARY | 4       | NULL | 9880 | Using where |
 
+------+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+

10.5 EXPLAIN

+------+--------------+----------------+--------+---------------+---------+---------+--------------+-------+----------------+
 
| id   | select_type  | table          | type   | possible_keys | key     | key_len | ref          | rows  | Extra          |
 
+------+--------------+----------------+--------+---------------+---------+---------+--------------+-------+----------------+
 
|    1 | PRIMARY      | <subquery2>    | ALL    | distinct_key  | NULL    | NULL    | NULL         | 10000 |                |
 
|    1 | PRIMARY      | mytable        | eq_ref | PRIMARY       | PRIMARY | 4       | txx_0._col_1 | 1     | Using where    |
 
|    2 | MATERIALIZED | <derived3>     | ALL    | NULL          | NULL    | NULL    | NULL         | 10000 |                |
 
|    3 | DERIVED      | NULL           | NULL   | NULL          | NULL    | NULL    | NULL         | NULL  | No tables used |
 
+------+--------------+----------------+--------+---------------+---------+---------+--------------+-------+----------------+

Have tried with eq_range_index_dive_limit = 12000 and eq_range_index_dive_limit = 0, no difference in performance.

Have tried SET optimizer_switch='extended_keys=on,rowid_filter=on';, no difference in performance.

 Comments   
Comment by Sergei Petrunia [ 2021-10-26 ]

The provided EXPLAIN looks like a reasonable plan. Its execution speed should not be much different from the 5.5's EXPLAIN (lines 1, 3, and 4 look like extra work but actually they are not - MariaDB 5.5 will do nearly the same: sort IN-list, remove duplicates, then use it to make lookups into table mytable.

Comment by Sergei Petrunia [ 2021-11-25 ]

Closing as cannot reproduce. Feel free to re-open if the issue is observed [again].

Generated at Thu Feb 08 09:48:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.