|
Thanks a lot for the report!
As a workaround, you can try setting optimizer_switch='rowid_filter=off'
--source include/have_sequence.inc
|
--source include/have_innodb.inc
|
|
create table t1 ( a int, b int, key (b), key (a)) engine=innodb;
|
insert into t1 select seq+10000, 2 from seq_1_to_1000;
|
insert into t1 select 10100, 2 from seq_1_to_50;
|
|
select count(*) from t1 where a in(10100,1) and b = 2;
|
|
set optimizer_switch='rowid_filter=off';
|
select count(*) from t1 where a in(10100,1) and b = 2;
|
|
drop table t1;
|
MariaDB [test]> select count(*) from t1 where a in(10100,1) and b = 2;
|
+----------+
|
| count(*) |
|
+----------+
|
| 0 |
|
+----------+
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> set optimizer_switch='rowid_filter=off';
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> select count(*) from t1 where a in(10100,1) and b = 2;
|
+----------+
|
| count(*) |
|
+----------+
|
| 51 |
|
+----------+
|
1 row in set (0.001 sec)
|
|
|
If I add primary key to the table the query works fine:
create table t1 (pk int not null auto_increment, a int, b int, primary key (pk), key (b), key (a)) engine=innodb;
|
insert into t1(a,b) select seq+10000, 2 from seq_1_to_1000;
|
insert into t1(a,b) select 10100, 2 from seq_1_to_50;
|
MariaDB [test]> select count(*) from t1 where a in(10100,1) and b = 2;
|
+----------+
|
| count(*) |
|
+----------+
|
| 51 |
|
+----------+
|
1 row in set (0.007 sec)
|
MariaDB [test]> explain extended select count(*) from t1 where a in(10100,1) and b = 2;
|
+------+-------------+-------+------------+---------------+------+---------+-------+-----------+----------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------------+---------------+------+---------+-------+-----------+----------+---------------------------------+
|
| 1 | SIMPLE | t1 | ref|filter | b,a | b|a | 5|5 | const | 1050 (5%) | 4.95 | Using where; Using rowid filter |
|
+------+-------------+-------+------------+---------------+------+---------+-------+-----------+----------+---------------------------------+
|
1 row in set, 1 warning (0.004 sec)
|
MariaDB [test]> analyze format=json select count(*) from t1 where a in(10100,1) and b = 2;
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ANALYZE |
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 5.8473,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ref",
|
"possible_keys": ["b", "a"],
|
"key": "b",
|
"key_length": "5",
|
"used_key_parts": ["b"],
|
"ref": ["const"],
|
"rowid_filter": {
|
"range": {
|
"key": "a",
|
"used_key_parts": ["a"]
|
},
|
"rows": 52,
|
"selectivity_pct": 4.9524,
|
"r_rows": 51,
|
"r_selectivity_pct": 4.8571,
|
"r_buffer_size": 25,
|
"r_filling_time_ms": 0.9365
|
},
|
"r_loops": 1,
|
"rows": 1050,
|
"r_rows": 51,
|
"r_total_time_ms": 5.6406,
|
"filtered": 4.9524,
|
"r_filtered": 100,
|
"attached_condition": "t1.a in (10100,1)"
|
}
|
}
|
} |
|
|
execution.log
