Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.6, 10.4(EOL)
-
None
-
Server : Debian baremetal server, this is the only product installed. Data is stored on a 4x1Tb SSD with material RAID 5
Client : Tested with mycli / mysql command / dbeaver on 2 different workstation on debian
Description
After migrating from mariadb 10.1.38-0+deb9u1 (provided by debian) to 10.4.6+maria~stretch, some request including IN (or multiple OR) and filtering on multiple columns indexed separately :
Here is the table creation : table_creation.sql
Here are the logs with my different results : execution.log
Execution plan : execution_plan.log
If I add an index on the two columns, the results are back : workaround.log
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
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)"
|
}
|
}
|
} |
|
I suggested a little shorter fix. In the case when the rowid filter does not match, it will do some extra work and convert all available fields of the index record, not only the rowid column.
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.incMariaDB [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)