Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
None
-
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.