|
Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1
If I run:
EXPLAIN SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4"
|
This query uses the index on the "ip" column (simple index).
However, if I run (simplest example query I could get):
EXPLAIN SELECT srl.id
|
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`=srl.ip) as ipcount
|
FROM srl
|
LIMIT 10
|
The subquery on the "ips" table doesn't have "ip" as "possible_keys", it uses the "time" index (which isn't even used in the query!), and does a table scan (and this is a huge table).
If I put "FORCE INDEX(ip)", the EXPLAIN says it uses the "ip" key, but it still does a table scan.
If I hardcode an IP, like:
EXPLAIN SELECT srl.id
|
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4") as ipcount
|
FROM srl
|
LIMIT 10
|
It uses the "ip" key as intended.
What is preventing the subquery from using the index as supposed, in the `ip`=srl.ip example?
-------
UPDATE:
ANALYZE FORMAT=JSON
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 19537.7025,
|
"table": {
|
"table_name": "srl",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 92181,
|
"r_rows": 10,
|
"r_table_time_ms": 0.055588922,
|
"r_other_time_ms": 0.116198584,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"subqueries": [
|
{
|
"expression_cache": {
|
"r_loops": 10,
|
"r_hit_ratio": 30,
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 7,
|
"r_total_time_ms": 19537.40246,
|
"table": {
|
"table_name": "ips",
|
"access_type": "index",
|
"key": "time",
|
"key_length": "4",
|
"used_key_parts": ["time"],
|
"r_loops": 7,
|
"rows": 4948857,
|
"r_rows": 5949193,
|
"r_table_time_ms": 12379.15752,
|
"r_other_time_ms": 7158.182676,
|
"filtered": 100,
|
"r_filtered": 1.656887e-4,
|
"attached_condition": "convert(ips.ip using utf8mb4) = srl.ip",
|
"using_index": true
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
I guess `attached_condition` tells me what it is! One is `utf8_general_ci`, the other is `utf8mb4_unicode_ci`, so MariaDB converts one of them, to match the collation. I didn't know about `ANALYZE` ! Great stuff!
Maybe a suggestion would be to add `attached_condition` in EXPLAIN EXTENDED or so (as it seems that EXPLAIN by itself already predicts this does happen), and it would greatly help!
|
