Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
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!
Attachments
Issue Links
- relates to
-
-
- Open
-
- links to
Activity
Thanks, but as per the description above, I "figured it out" by using the ANALYZE. It's because of the different collations.
Then this ticket was converted to a suggestion, to make EXPLAIN EXTENDED tell this to us straight away.
Ironically, I had the exact same problem earlier today, where a query wasn't performing at all, when I was "sure" the JOINs and Indexes were all fine!! But the EXPLAIN was showing "Temp table & Filesort"...
Then I checked ANALYZE, and found the same collation issue (and fixed it).
Again, it would have saved me time if EXPLAIN EXTENDED told me straight away that there is a collation conversion involved.
And I've just realized that "EXPLAIN FORMAT=JSON" also includes "attached_condition" in it.
Yeah... it would be very helpful if the output of EXPLAIN (on table format) included "attached_condition" by default 
@nuno - Please provide `SHOW CREATE TABLE` for the two tables so we can see the datatypes and indexes.