|
Updated testcase:
create table t10 (a int, b int, c int, key(a,b));
|
insert into t10 select seq, seq, seq from seq_1_to_10000;
|
explain format=json select * from t10 where a<3 and b!=5 and c<10;
|
| {
|
"query_block": {
|
"select_id": 1,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t10",
|
"access_type": "range",
|
"possible_keys": ["a"],
|
"key": "a",
|
"key_length": "5",
|
"used_key_parts": ["a"],
|
"rows": 2,
|
"filtered": 100,
|
"index_condition": "t10.a < 3 and t10.b <> 5",
|
"attached_condition": "t10.c < 10"
|
}
|
}
|
]
|
}
|
} |
|
Here, we see that there is an attached condition and an index condition.
However, this is not reflected in the trace:
|
select json_detailed(json_extract(trace, '$**.attaching_conditions_to_tables')) as out1 from information_schema.optimizer_trace\G
|
*************************** 1. row ***************************
|
out1: [
|
{
|
"attached_conditions_computation":
|
[
|
],
|
"attached_conditions_summary":
|
[
|
{
|
"table": "t10",
|
"attached": "t10.a < 3 and t10.b <> 5 and t10.c < 10"
|
}
|
]
|
}
|
]
|
|
|
Hi Sergei, was looking into opt_range_mrr.cc looks like it could cover RANGE RANGE but not found any difference in explain
MariaDB [test]> explain format=json select * from t where a<10 and b in (1,2) and c<10;
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t",
|
"access_type": "range",
|
"possible_keys": ["a"],
|
"key": "a",
|
"key_length": "5",
|
"used_key_parts": ["a"],
|
"rows": 9,
|
"filtered": 100,
|
"index_condition": "t.a < 10 and t.b in (1,2)",
|
"attached_condition": "t.c < 10",
|
"mrr_type": "Rowid-ordered scan"
|
}
|
}
|
} |
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.002 sec)
|
analyze select * from t where a<10 and b in (1,2) and c<10;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
| 1 | SIMPLE | t | range | a | a | 5 | NULL | 9 | 2.00 | 100.00 | 100.00 | Using index condition; Using where; Rowid-ordered scan |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
1 row in set (0.002 sec)
|
MariaDB [test]> explain format=json select * from t where a<10 and b <> 4 and c<10;
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t",
|
"access_type": "range",
|
"possible_keys": ["a"],
|
"key": "a",
|
"key_length": "5",
|
"used_key_parts": ["a"],
|
"rows": 9,
|
"filtered": 100,
|
"index_condition": "t.a < 10 and t.b <> 4",
|
"attached_condition": "t.c < 10",
|
"mrr_type": "Rowid-ordered scan"
|
}
|
}
|
} |
|
MariaDB [test]> analyze select * from t where a<10 and b <> 4 and c<10;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
| 1 | SIMPLE | t | range | a | a | 5 | NULL | 9 | 8.00 | 100.00 | 100.00 | Using index condition; Using where; Rowid-ordered scan |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
Should not attache condition in second plan be: "t.c < 10 t.b <> 4" and index_condition be "ta.a <10" and to differentiate with first plan ?
|