[MDEV-27146] extend EXPLAIN output to include "attached_condition" - Jira

Nuno created issue - 2021-11-30 21:46

Nuno made changes - 2021-11-30 21:46

Field	Original Value	New Value
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?	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?

Nuno made changes - 2021-11-30 21:47

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?

Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1

If I run:

{code:sql}
EXPLAIN SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4"
{code}

This query uses the index on the "ip" column (simple index).

However, if I run (simplest example query I could get):

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`=srl.ip) as ipcount
FROM srl
LIMIT 10
{code}

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:

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4") as ipcount
FROM srl
LIMIT 10
{code}

It uses the "ip" key as intended.

What is preventing the subquery from using the index as supposed, in the `ip`=srl.ip example?

Nuno made changes - 2021-11-30 23:03

Description

Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1

If I run:

{code:sql}
EXPLAIN SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4"
{code}

This query uses the index on the "ip" column (simple index).

However, if I run (simplest example query I could get):

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`=srl.ip) as ipcount
FROM srl
LIMIT 10
{code}

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:

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4") as ipcount
FROM srl
LIMIT 10
{code}

It uses the "ip" key as intended.

What is preventing the subquery from using the index as supposed, in the `ip`=srl.ip example?

Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1

If I run:

{code:sql}
EXPLAIN SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4"
{code}

This query uses the index on the "ip" column (simple index).

However, if I run (simplest example query I could get):

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`=srl.ip) as ipcount
FROM srl
LIMIT 10
{code}

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:

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4") as ipcount
FROM srl
LIMIT 10
{code}

It uses the "ip" key as intended.

*What is preventing the subquery from using the index as supposed, in the `ip`=srl.ip example?*

ANALYZE FORMAT=JSON

{noformat}
{
  "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
            }
          }
        }
      }
    ]
  }
}
{noformat}

Nuno made changes - 2021-11-30 23:08

Description

Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1

If I run:

{code:sql}
EXPLAIN SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4"
{code}

This query uses the index on the "ip" column (simple index).

However, if I run (simplest example query I could get):

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`=srl.ip) as ipcount
FROM srl
LIMIT 10
{code}

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:

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4") as ipcount
FROM srl
LIMIT 10
{code}

It uses the "ip" key as intended.

*What is preventing the subquery from using the index as supposed, in the `ip`=srl.ip example?*

ANALYZE FORMAT=JSON

{noformat}
{
  "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
            }
          }
        }
      }
    ]
  }
}
{noformat}

Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1

If I run:

{code:sql}
EXPLAIN SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4"
{code}

This query uses the index on the "ip" column (simple index).

However, if I run (simplest example query I could get):

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`=srl.ip) as ipcount
FROM srl
LIMIT 10
{code}

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:

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4") as ipcount
FROM srl
LIMIT 10
{code}

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

{noformat}
{
  "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
            }
          }
        }
      }
    ]
  }
}
{noformat}

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 mention that in EXPLAIN EXTENDED or so (as it seems that EXPLAIN by itself already predicts this does happen), and it would greatly help!

Nuno made changes - 2021-11-30 23:09

Description

Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1

If I run:

{code:sql}
EXPLAIN SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4"
{code}

This query uses the index on the "ip" column (simple index).

However, if I run (simplest example query I could get):

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`=srl.ip) as ipcount
FROM srl
LIMIT 10
{code}

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:

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4") as ipcount
FROM srl
LIMIT 10
{code}

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

{noformat}
{
  "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
            }
          }
        }
      }
    ]
  }
}
{noformat}

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 mention that in EXPLAIN EXTENDED or so (as it seems that EXPLAIN by itself already predicts this does happen), and it would greatly help!

Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1

If I run:

{code:sql}
EXPLAIN SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4"
{code}

This query uses the index on the "ip" column (simple index).

However, if I run (simplest example query I could get):

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`=srl.ip) as ipcount
FROM srl
LIMIT 10
{code}

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:

{code:sql}
EXPLAIN SELECT srl.id
,(SELECT COUNT( * ) FROM `ips` WHERE `ip`="1.2.3.4") as ipcount
FROM srl
LIMIT 10
{code}

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

{noformat}
{
  "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
            }
          }
        }
      }
    ]
  }
}
{noformat}

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!

Daniel Black made changes - 2021-12-01 00:21

Summary

Subquery not using Index, even if forcing

extend explain output to include "attached_condition"

Daniel Black made changes - 2021-12-01 00:22

Link

This issue relates to MDEV-17825 [ MDEV-17825 ]

Daniel Black made changes - 2021-12-01 00:22

Component/s		Optimizer [ 10200 ]
Component/s	Server [ 13907 ]

Daniel Black made changes - 2021-12-01 01:50

Remote Link

This issue links to "dba exchange: original question (Web Link)" [ 32623 ]

Sergei Golubchik made changes - 2021-12-06 21:21

Workflow

MariaDB v3 [ 128062 ]

MariaDB v4 [ 131605 ]

Rick James added a comment - 2022-04-14 01:30

@nuno - Please provide `SHOW CREATE TABLE` for the two tables so we can see the datatypes and indexes.

Rick James added a comment - 2022-04-14 01:30 @nuno - Please provide `SHOW CREATE TABLE` for the two tables so we can see the datatypes and indexes.

Nuno added a comment - 2022-04-14 01:35

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.

Nuno added a comment - 2022-04-14 01:35 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.

Nuno added a comment - 2022-04-20 16:51

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 added a comment - 2022-04-20 16:51 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 made changes - 2022-05-02 00:47

Summary

extend explain output to include "attached_condition"

extend EXPLAIN output to include "attached_condition"

MariaDB Server

extend EXPLAIN output to include "attached_condition"

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration