Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10922

optimization on count distinct on primary key

Details

    Description

      As all 3 queries just returns the number of rows in products, they should be able to run alot faster.

      SELECT COUNT(our_article_id) FROM products; -- 0.17s
      SELECT COUNT(DISTINCT our_article_id) FROM products; -- 0.99s
      SELECT COUNT(DISTINCT our_article_id) FROM products LEFT JOIN product_article_id USING (our_article_id); -- 3.32s
      

      CREATE TABLE products (
          our_article_id varchar(50) CHARACTER SET utf8 NOT NULL,
          PRIMARY KEY(our_article_id)
      ) ENGINE=InnoDB;
       
      CREATE TABLE product_article_id (
          article_id varchar(255) COLLATE utf8_bin NOT NULL,
          our_article_id varchar(50) CHARACTER SET utf8 NOT NULL,
          PRIMARY KEY(article_id),
          INDEX(our_article_id)
      ) ENGINE=InnoDB;
      

      // Add some data

      INSERT INTO products VALUES ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'), ('i'), ('j'), ('k'), ('l'), ('m'), ('n'), ('o'), ('p'), ('q'), ('r'), ('s'), ('t'), ('u'), ('v'), ('w'), ('x'), ('y'), ('z');
      INSERT INTO products SELECT CONCAT(a.our_article_id, b.our_article_id) FROM products AS a, products AS b;
      INSERT IGNORE INTO product_article_id SELECT CONCAT(a.our_article_id, b.our_article_id), b.our_article_id FROM products AS a, products AS b;
      INSERT IGNORE INTO products SELECT CONCAT(a.our_article_id, b.our_article_id) FROM products AS a, products AS b;
      

      A count on a non-null-column on a table witout where-parts or joins, is always the number of rows in that table?

      A distinct count non-null-uniqe-column on a table without where-parts, is always the number of rows in that table?

      A distinct count on a table, is not influenced by a left join of another table, if that table have no where-parts?

      Attachments

        Issue Links

          Activity

            dshjoshi Dipti Joshi (Inactive) added a comment - - edited

            puggan This issue is on MariaDB Server and not on MariaDB ColumnStore. It should be filed with the project "MDEV" and not "MCOL" Moving it to MDEV project.

            dshjoshi Dipti Joshi (Inactive) added a comment - - edited puggan This issue is on MariaDB Server and not on MariaDB ColumnStore. It should be filed with the project "MDEV" and not "MCOL" Moving it to MDEV project.
            danblack Daniel Black added a comment -

            With MDEV-30660 the first two queries are now equivalent:

            10.5.25-c32e59ac55ad8f5bbc3d069092d98b2d7d32eb8b

             
            MariaDB [test]> SELECT COUNT(our_article_id) FROM products; 
            +-----------------------+
            | COUNT(our_article_id) |
            +-----------------------+
            |                475254 |
            +-----------------------+
            1 row in set (0.057 sec)
             
            MariaDB [test]> SELECT COUNT(DISTINCT our_article_id) FROM products; 
            +--------------------------------+
            | COUNT(DISTINCT our_article_id) |
            +--------------------------------+
            |                         475254 |
            +--------------------------------+
            1 row in set (0.046 sec)
             
            MariaDB [test]> analyze format=json SELECT COUNT(DISTINCT our_article_id) FROM products\G
            *************************** 1. row ***************************
            ANALYZE: {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_total_time_ms": 54.03990255,
                "table": {
                  "table_name": "products",
                  "access_type": "index",
                  "key": "PRIMARY",
                  "key_length": "152",
                  "used_key_parts": ["our_article_id"],
                  "r_loops": 1,
                  "rows": 487710,
                  "r_rows": 475254,
                  "r_table_time_ms": 43.4104289,
                  "r_other_time_ms": 10.62132229,
                  "filtered": 100,
                  "r_filtered": 100,
                  "using_index": true
                }
              }
            }
            1 row in set (0.054 sec)
             
            MariaDB [test]> analyze format=json SELECT COUNT(our_article_id) FROM products\G
            *************************** 1. row ***************************
            ANALYZE: {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_total_time_ms": 52.0385043,
                "table": {
                  "table_name": "products",
                  "access_type": "index",
                  "key": "PRIMARY",
                  "key_length": "152",
                  "used_key_parts": ["our_article_id"],
                  "r_loops": 1,
                  "rows": 487710,
                  "r_rows": 475254,
                  "r_table_time_ms": 41.93969579,
                  "r_other_time_ms": 10.08955024,
                  "filtered": 100,
                  "r_filtered": 100,
                  "using_index": true
                }
              }
            }
            1 row in set (0.052 sec)
            

            danblack Daniel Black added a comment - With MDEV-30660 the first two queries are now equivalent: 10.5.25-c32e59ac55ad8f5bbc3d069092d98b2d7d32eb8b   MariaDB [test]> SELECT COUNT(our_article_id) FROM products; +-----------------------+ | COUNT(our_article_id) | +-----------------------+ | 475254 | +-----------------------+ 1 row in set (0.057 sec)   MariaDB [test]> SELECT COUNT(DISTINCT our_article_id) FROM products; +--------------------------------+ | COUNT(DISTINCT our_article_id) | +--------------------------------+ | 475254 | +--------------------------------+ 1 row in set (0.046 sec)   MariaDB [test]> analyze format=json SELECT COUNT(DISTINCT our_article_id) FROM products\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 54.03990255, "table": { "table_name": "products", "access_type": "index", "key": "PRIMARY", "key_length": "152", "used_key_parts": ["our_article_id"], "r_loops": 1, "rows": 487710, "r_rows": 475254, "r_table_time_ms": 43.4104289, "r_other_time_ms": 10.62132229, "filtered": 100, "r_filtered": 100, "using_index": true } } } 1 row in set (0.054 sec)   MariaDB [test]> analyze format=json SELECT COUNT(our_article_id) FROM products\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 52.0385043, "table": { "table_name": "products", "access_type": "index", "key": "PRIMARY", "key_length": "152", "used_key_parts": ["our_article_id"], "r_loops": 1, "rows": 487710, "r_rows": 475254, "r_table_time_ms": 41.93969579, "r_other_time_ms": 10.08955024, "filtered": 100, "r_filtered": 100, "using_index": true } } } 1 row in set (0.052 sec)
            danblack Daniel Black added a comment -

            There still isn't the table elimination of product_article_id in the query 3, and it doesn't pick up the "aggregator_type": "simple":

            MariaDB [test]> analyze format=json sELECT COUNT(DISTINCT our_article_id) FROM products LEFT JOIN product_article_id USING (our_article_id)\G
            *************************** 1. row ***************************
            ANALYZE: {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_total_time_ms": 788.1020659,
                "const_condition": "1",
                "table": {
                  "table_name": "products",
                  "access_type": "index",
                  "key": "PRIMARY",
                  "key_length": "152",
                  "used_key_parts": ["our_article_id"],
                  "r_loops": 1,
                  "rows": 487710,
                  "r_rows": 475254,
                  "r_table_time_ms": 45.03326833,
                  "r_other_time_ms": 49.49011691,
                  "filtered": 100,
                  "r_filtered": 100,
                  "using_index": true
                },
                "table": {
                  "table_name": "product_article_id",
                  "access_type": "ref",
                  "possible_keys": ["our_article_id"],
                  "key": "our_article_id",
                  "key_length": "152",
                  "used_key_parts": ["our_article_id"],
                  "ref": ["test.products.our_article_id"],
                  "r_loops": 475254,
                  "rows": 1,
                  "r_rows": 0.999945292,
                  "r_table_time_ms": 445.3546335,
                  "r_other_time_ms": 248.2149302,
                  "filtered": 100,
                  "r_filtered": 100,
                  "using_index": true
                }
              }
            }
            1 row in set (0.789 sec)
             
            MariaDB [test]> select trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
            *************************** 1. row ***************************
            trace: {
              "steps": [
                {
                  "join_preparation": {
                    "select_id": 1,
                    "steps": [
                      {
                        "expanded_query": "select count(distinct products.our_article_id) AS `COUNT(DISTINCT our_article_id)` from (products left join product_article_id on(products.our_article_id = product_article_id.our_article_id))"
                      }
                    ]
                  }
                },
                {
                  "join_optimization": {
                    "select_id": 1,
                    "steps": [
                      {
                        "table_dependencies": [
                          {
                            "table": "products",
                            "row_may_be_null": false,
                            "map_bit": 0,
                            "depends_on_map_bits": []
                          },
                          {
                            "table": "product_article_id",
                            "row_may_be_null": true,
                            "map_bit": 1,
                            "depends_on_map_bits": ["0"]
                          }
                        ]
                      },
                      {
                        "ref_optimizer_key_uses": [
                          {
                            "table": "product_article_id",
                            "field": "our_article_id",
                            "equals": "products.our_article_id",
                            "null_rejecting": false
                          }
                        ]
                      },
                      {
                        "eliminated_tables": []
                      },
                      {
                        "rows_estimation": [
                          {
                            "table": "products",
                            "table_scan": {
                              "rows": 487710,
                              "cost": 1571
                            }
                          },
                          {
                            "table": "product_article_id",
                            "table_scan": {
                              "rows": 455516,
                              "cost": 1827
                            }
                          }
                        ]
                      },
                      {
                        "considered_execution_plans": [
                          {
                            "plan_prefix": [],
                            "table": "products",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 487710,
                                  "cost": 1571,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 487710,
                                "cost": 1571,
                                "uses_join_buffering": false
                              }
                            },
                            "rows_for_plan": 487710,
                            "cost_for_plan": 99113,
                            "rest_of_plan": [
                              {
                                "plan_prefix": ["products"],
                                "table": "product_article_id",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "ref",
                                      "index": "our_article_id",
                                      "rows": 1,
                                      "cost": 493180.9245,
                                      "chosen": true
                                    },
                                    {
                                      "type": "scan",
                                      "chosen": false,
                                      "cause": "cost"
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "ref",
                                    "records": 1,
                                    "cost": 493180.9245,
                                    "uses_join_buffering": false
                                  }
                                },
                                "rows_for_plan": 487710,
                                "cost_for_plan": 689835.9245,
                                "estimated_join_cardinality": 487710
                              }
                            ]
                          }
                        ]
                      },
                      {
                        "best_join_order": ["products", "product_article_id"]
                      },
                      {
                        "condition_on_constant_tables": "1",
                        "computing_condition": []
                      },
                      {
                        "attaching_conditions_to_tables": {
                          "original_condition": "1",
                          "attached_conditions_computation": [],
                          "attached_conditions_summary": [
                            {
                              "table": "products",
                              "attached": null
                            },
                            {
                              "table": "product_article_id",
                              "attached": null
                            }
                          ]
                        }
                      },
                      {
                        "prepare_sum_aggregators": {
                          "function": "count(distinct products.our_article_id)",
                          "aggregator_type": "distinct"
                        }
                      }
                    ]
                  }
                },
                {
                  "join_execution": {
                    "select_id": 1,
                    "steps": []
                  }
                }
              ]
            }
            1 row in set (0.001 sec)
             
            MariaDB [test]> analyze format=json sELECT COUNT(our_article_id) FROM products LEFT JOIN product_article_id USING (our_article_id)\G
            *************************** 1. row ***************************
            ANALYZE: {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_total_time_ms": 501.8227966,
                "const_condition": "1",
                "table": {
                  "table_name": "products",
                  "access_type": "index",
                  "key": "PRIMARY",
                  "key_length": "152",
                  "used_key_parts": ["our_article_id"],
                  "r_loops": 1,
                  "rows": 487710,
                  "r_rows": 475254,
                  "r_table_time_ms": 42.74716398,
                  "r_other_time_ms": 28.20611528,
                  "filtered": 100,
                  "r_filtered": 100,
                  "using_index": true
                },
                "table": {
                  "table_name": "product_article_id",
                  "access_type": "ref",
                  "possible_keys": ["our_article_id"],
                  "key": "our_article_id",
                  "key_length": "152",
                  "used_key_parts": ["our_article_id"],
                  "ref": ["test.products.our_article_id"],
                  "r_loops": 475254,
                  "rows": 1,
                  "r_rows": 0.999945292,
                  "r_table_time_ms": 401.9788198,
                  "r_other_time_ms": 28.8808535,
                  "filtered": 100,
                  "r_filtered": 100,
                  "using_index": true
                }
              }
            }
            1 row in set (0.501 sec)
             
            MariaDB [test]> select trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
            *************************** 1. row ***************************
            trace: {
              "steps": [
                {
                  "join_preparation": {
                    "select_id": 1,
                    "steps": [
                      {
                        "expanded_query": "select count(products.our_article_id) AS `COUNT(our_article_id)` from (products left join product_article_id on(products.our_article_id = product_article_id.our_article_id))"
                      }
                    ]
                  }
                },
                {
                  "join_optimization": {
                    "select_id": 1,
                    "steps": [
                      {
                        "table_dependencies": [
                          {
                            "table": "products",
                            "row_may_be_null": false,
                            "map_bit": 0,
                            "depends_on_map_bits": []
                          },
                          {
                            "table": "product_article_id",
                            "row_may_be_null": true,
                            "map_bit": 1,
                            "depends_on_map_bits": ["0"]
                          }
                        ]
                      },
                      {
                        "ref_optimizer_key_uses": [
                          {
                            "table": "product_article_id",
                            "field": "our_article_id",
                            "equals": "products.our_article_id",
                            "null_rejecting": false
                          }
                        ]
                      },
                      {
                        "eliminated_tables": []
                      },
                      {
                        "rows_estimation": [
                          {
                            "table": "products",
                            "table_scan": {
                              "rows": 487710,
                              "cost": 1571
                            }
                          },
                          {
                            "table": "product_article_id",
                            "table_scan": {
                              "rows": 455516,
                              "cost": 1827
                            }
                          }
                        ]
                      },
                      {
                        "considered_execution_plans": [
                          {
                            "plan_prefix": [],
                            "table": "products",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 487710,
                                  "cost": 1571,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 487710,
                                "cost": 1571,
                                "uses_join_buffering": false
                              }
                            },
                            "rows_for_plan": 487710,
                            "cost_for_plan": 99113,
                            "rest_of_plan": [
                              {
                                "plan_prefix": ["products"],
                                "table": "product_article_id",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "ref",
                                      "index": "our_article_id",
                                      "rows": 1,
                                      "cost": 493180.9245,
                                      "chosen": true
                                    },
                                    {
                                      "type": "scan",
                                      "chosen": false,
                                      "cause": "cost"
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "ref",
                                    "records": 1,
                                    "cost": 493180.9245,
                                    "uses_join_buffering": false
                                  }
                                },
                                "rows_for_plan": 487710,
                                "cost_for_plan": 689835.9245,
                                "estimated_join_cardinality": 487710
                              }
                            ]
                          }
                        ]
                      },
                      {
                        "best_join_order": ["products", "product_article_id"]
                      },
                      {
                        "condition_on_constant_tables": "1",
                        "computing_condition": []
                      },
                      {
                        "attaching_conditions_to_tables": {
                          "original_condition": "1",
                          "attached_conditions_computation": [],
                          "attached_conditions_summary": [
                            {
                              "table": "products",
                              "attached": null
                            },
                            {
                              "table": "product_article_id",
                              "attached": null
                            }
                          ]
                        }
                      },
                      {
                        "prepare_sum_aggregators": {
                          "function": "count(products.our_article_id)",
                          "aggregator_type": "simple"
                        }
                      }
                    ]
                  }
                },
                {
                  "join_execution": {
                    "select_id": 1,
                    "steps": []
                  }
                }
              ]
            }
            1 row in set (0.001 sec)
            

            danblack Daniel Black added a comment - There still isn't the table elimination of product_article_id in the query 3, and it doesn't pick up the "aggregator_type": "simple": MariaDB [test]> analyze format=json sELECT COUNT(DISTINCT our_article_id) FROM products LEFT JOIN product_article_id USING (our_article_id)\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 788.1020659, "const_condition": "1", "table": { "table_name": "products", "access_type": "index", "key": "PRIMARY", "key_length": "152", "used_key_parts": ["our_article_id"], "r_loops": 1, "rows": 487710, "r_rows": 475254, "r_table_time_ms": 45.03326833, "r_other_time_ms": 49.49011691, "filtered": 100, "r_filtered": 100, "using_index": true }, "table": { "table_name": "product_article_id", "access_type": "ref", "possible_keys": ["our_article_id"], "key": "our_article_id", "key_length": "152", "used_key_parts": ["our_article_id"], "ref": ["test.products.our_article_id"], "r_loops": 475254, "rows": 1, "r_rows": 0.999945292, "r_table_time_ms": 445.3546335, "r_other_time_ms": 248.2149302, "filtered": 100, "r_filtered": 100, "using_index": true } } } 1 row in set (0.789 sec)   MariaDB [test]> select trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** trace: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select count(distinct products.our_article_id) AS `COUNT(DISTINCT our_article_id)` from (products left join product_article_id on(products.our_article_id = product_article_id.our_article_id))" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "table_dependencies": [ { "table": "products", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "product_article_id", "row_may_be_null": true, "map_bit": 1, "depends_on_map_bits": ["0"] } ] }, { "ref_optimizer_key_uses": [ { "table": "product_article_id", "field": "our_article_id", "equals": "products.our_article_id", "null_rejecting": false } ] }, { "eliminated_tables": [] }, { "rows_estimation": [ { "table": "products", "table_scan": { "rows": 487710, "cost": 1571 } }, { "table": "product_article_id", "table_scan": { "rows": 455516, "cost": 1827 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "products", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 487710, "cost": 1571, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 487710, "cost": 1571, "uses_join_buffering": false } }, "rows_for_plan": 487710, "cost_for_plan": 99113, "rest_of_plan": [ { "plan_prefix": ["products"], "table": "product_article_id", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "our_article_id", "rows": 1, "cost": 493180.9245, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 493180.9245, "uses_join_buffering": false } }, "rows_for_plan": 487710, "cost_for_plan": 689835.9245, "estimated_join_cardinality": 487710 } ] } ] }, { "best_join_order": ["products", "product_article_id"] }, { "condition_on_constant_tables": "1", "computing_condition": [] }, { "attaching_conditions_to_tables": { "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "products", "attached": null }, { "table": "product_article_id", "attached": null } ] } }, { "prepare_sum_aggregators": { "function": "count(distinct products.our_article_id)", "aggregator_type": "distinct" } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 1 row in set (0.001 sec)   MariaDB [test]> analyze format=json sELECT COUNT(our_article_id) FROM products LEFT JOIN product_article_id USING (our_article_id)\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 501.8227966, "const_condition": "1", "table": { "table_name": "products", "access_type": "index", "key": "PRIMARY", "key_length": "152", "used_key_parts": ["our_article_id"], "r_loops": 1, "rows": 487710, "r_rows": 475254, "r_table_time_ms": 42.74716398, "r_other_time_ms": 28.20611528, "filtered": 100, "r_filtered": 100, "using_index": true }, "table": { "table_name": "product_article_id", "access_type": "ref", "possible_keys": ["our_article_id"], "key": "our_article_id", "key_length": "152", "used_key_parts": ["our_article_id"], "ref": ["test.products.our_article_id"], "r_loops": 475254, "rows": 1, "r_rows": 0.999945292, "r_table_time_ms": 401.9788198, "r_other_time_ms": 28.8808535, "filtered": 100, "r_filtered": 100, "using_index": true } } } 1 row in set (0.501 sec)   MariaDB [test]> select trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** trace: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select count(products.our_article_id) AS `COUNT(our_article_id)` from (products left join product_article_id on(products.our_article_id = product_article_id.our_article_id))" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "table_dependencies": [ { "table": "products", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "product_article_id", "row_may_be_null": true, "map_bit": 1, "depends_on_map_bits": ["0"] } ] }, { "ref_optimizer_key_uses": [ { "table": "product_article_id", "field": "our_article_id", "equals": "products.our_article_id", "null_rejecting": false } ] }, { "eliminated_tables": [] }, { "rows_estimation": [ { "table": "products", "table_scan": { "rows": 487710, "cost": 1571 } }, { "table": "product_article_id", "table_scan": { "rows": 455516, "cost": 1827 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "products", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 487710, "cost": 1571, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 487710, "cost": 1571, "uses_join_buffering": false } }, "rows_for_plan": 487710, "cost_for_plan": 99113, "rest_of_plan": [ { "plan_prefix": ["products"], "table": "product_article_id", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "our_article_id", "rows": 1, "cost": 493180.9245, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 493180.9245, "uses_join_buffering": false } }, "rows_for_plan": 487710, "cost_for_plan": 689835.9245, "estimated_join_cardinality": 487710 } ] } ] }, { "best_join_order": ["products", "product_article_id"] }, { "condition_on_constant_tables": "1", "computing_condition": [] }, { "attaching_conditions_to_tables": { "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "products", "attached": null }, { "table": "product_article_id", "attached": null } ] } }, { "prepare_sum_aggregators": { "function": "count(products.our_article_id)", "aggregator_type": "simple" } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 1 row in set (0.001 sec)
            danblack Daniel Black added a comment -

            Also note, under Aria, query 1 is a lot faster than query 2 (which was improved by MDEV-30660):

            MariaDB [test]>  alter table products engine=Aria;
            Query OK, 475254 rows affected (0.433 sec)             
            Records: 475254  Duplicates: 0  Warnings: 0
             
            MariaDB [test]> SELECT COUNT(our_article_id) FROM products;
            +-----------------------+
            | COUNT(our_article_id) |
            +-----------------------+
            |                475254 |
            +-----------------------+
            1 row in set (0.001 sec)
             
            MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
            *************************** 1. row ***************************
                                        QUERY: SELECT COUNT(our_article_id) FROM products
                                        TRACE: {
              "steps": [
                {
                  "join_preparation": {
                    "select_id": 1,
                    "steps": [
                      {
                        "expanded_query": "select count(products.our_article_id) AS `COUNT(our_article_id)` from products"
                      }
                    ]
                  }
                },
                {
                  "join_optimization": {
                    "select_id": 1,
                    "steps": []
                  }
                },
                {
                  "join_execution": {
                    "select_id": 1,
                    "steps": []
                  }
                }
              ]
            }
            MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
                      INSUFFICIENT_PRIVILEGES: 0
            1 row in set (0.001 sec)
             
            MariaDB [test]> SELECT COUNT(DISTINCT our_article_id) FROM products\G
            *************************** 1. row ***************************
            COUNT(DISTINCT our_article_id): 475254
            1 row in set (0.057 sec)
             
            MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
            *************************** 1. row ***************************
                                        QUERY: SELECT COUNT(DISTINCT our_article_id) FROM products
                                        TRACE: {
              "steps": [
                {
                  "join_preparation": {
                    "select_id": 1,
                    "steps": [
                      {
                        "expanded_query": "select count(distinct products.our_article_id) AS `COUNT(DISTINCT our_article_id)` from products"
                      }
                    ]
                  }
                },
                {
                  "join_optimization": {
                    "select_id": 1,
                    "steps": [
                      {
                        "table_dependencies": [
                          {
                            "table": "products",
                            "row_may_be_null": false,
                            "map_bit": 0,
                            "depends_on_map_bits": []
                          }
                        ]
                      },
                      {
                        "rows_estimation": [
                          {
                            "table": "products",
                            "range_analysis": {
                              "table_scan": {
                                "rows": 475254,
                                "cost": 97504.8
                              },
                              "potential_range_indexes": [
                                {
                                  "index": "PRIMARY",
                                  "usable": true,
                                  "key_parts": ["our_article_id"]
                                }
                              ],
                              "best_covering_index_scan": {
                                "index": "PRIMARY",
                                "cost": 109069.8314,
                                "chosen": false,
                                "cause": "cost"
                              },
                              "group_index_range": {
                                "potential_group_range_indexes": [
                                  {
                                    "index": "PRIMARY",
                                    "covering": true,
                                    "usable": false,
                                    "cause": "using unique index"
                                  }
                                ]
                              }
                            }
                          }
                        ]
                      },
                      {
                        "considered_execution_plans": [
                          {
                            "plan_prefix": [],
                            "table": "products",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 475254,
                                  "cost": 2452,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 475254,
                                "cost": 2452,
                                "uses_join_buffering": false
                              }
                            },
                            "rows_for_plan": 475254,
                            "cost_for_plan": 97502.8,
                            "estimated_join_cardinality": 475254
                          }
                        ]
                      },
                      {
                        "best_join_order": ["products"]
                      },
                      {
                        "attaching_conditions_to_tables": {
                          "original_condition": null,
                          "attached_conditions_computation": [],
                          "attached_conditions_summary": [
                            {
                              "table": "products",
                              "attached": null
                            }
                          ]
                        }
                      },
                      {
                        "prepare_sum_aggregators": {
                          "function": "count(distinct products.our_article_id)",
                          "aggregator_type": "simple"
                        }
                      }
                    ]
                  }
                },
                {
                  "join_execution": {
                    "select_id": 1,
                    "steps": []
                  }
                }
              ]
            }
            MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
                      INSUFFICIENT_PRIVILEGES: 0
            1 row in set (0.001 sec)
            

            danblack Daniel Black added a comment - Also note, under Aria, query 1 is a lot faster than query 2 (which was improved by MDEV-30660 ): MariaDB [test]> alter table products engine=Aria; Query OK, 475254 rows affected (0.433 sec) Records: 475254 Duplicates: 0 Warnings: 0   MariaDB [test]> SELECT COUNT(our_article_id) FROM products; +-----------------------+ | COUNT(our_article_id) | +-----------------------+ | 475254 | +-----------------------+ 1 row in set (0.001 sec)   MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: SELECT COUNT(our_article_id) FROM products TRACE: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select count(products.our_article_id) AS `COUNT(our_article_id)` from products" } ] } }, { "join_optimization": { "select_id": 1, "steps": [] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.001 sec)   MariaDB [test]> SELECT COUNT(DISTINCT our_article_id) FROM products\G *************************** 1. row *************************** COUNT(DISTINCT our_article_id): 475254 1 row in set (0.057 sec)   MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: SELECT COUNT(DISTINCT our_article_id) FROM products TRACE: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select count(distinct products.our_article_id) AS `COUNT(DISTINCT our_article_id)` from products" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "table_dependencies": [ { "table": "products", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "products", "range_analysis": { "table_scan": { "rows": 475254, "cost": 97504.8 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["our_article_id"] } ], "best_covering_index_scan": { "index": "PRIMARY", "cost": 109069.8314, "chosen": false, "cause": "cost" }, "group_index_range": { "potential_group_range_indexes": [ { "index": "PRIMARY", "covering": true, "usable": false, "cause": "using unique index" } ] } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "products", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 475254, "cost": 2452, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 475254, "cost": 2452, "uses_join_buffering": false } }, "rows_for_plan": 475254, "cost_for_plan": 97502.8, "estimated_join_cardinality": 475254 } ] }, { "best_join_order": ["products"] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "products", "attached": null } ] } }, { "prepare_sum_aggregators": { "function": "count(distinct products.our_article_id)", "aggregator_type": "simple" } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.001 sec)

            Also note, under Aria, query 1 is a lot faster than query 2 (which was improved by MDEV-30660):

            danblack, this is because SELECT COUNT(our_article_id) FROM products; is the same as SELECT COUNT for a not-null column. And Aria like MyISAM has can provide exact count of records in the table.

            psergei Sergei Petrunia added a comment - Also note, under Aria, query 1 is a lot faster than query 2 (which was improved by MDEV-30660 ): danblack , this is because SELECT COUNT(our_article_id) FROM products; is the same as SELECT COUNT for a not-null column. And Aria like MyISAM has can provide exact count of records in the table.
            psergei Sergei Petrunia added a comment - - edited

            The JOIN query is interesting:

            explain SELECT COUNT(DISTINCT our_article_id) FROM products LEFT JOIN product_article_id USING (our_article_id); -- 3.32s
            +------+-------------+--------------------+-------+----------------+----------------+---------+-----------------------------+--------+-------------+
            | id   | select_type | table              | type  | possible_keys  | key            | key_len | ref                         | rows   | Extra       |
            +------+-------------+--------------------+-------+----------------+----------------+---------+-----------------------------+--------+-------------+
            |    1 | SIMPLE      | products           | index | NULL           | PRIMARY        | 152     | NULL                        | 430681 | Using index |
            |    1 | SIMPLE      | product_article_id | ref   | our_article_id | our_article_id | 152     | j10.products.our_article_id | 399    | Using index |
            +------+-------------+--------------------+-------+----------------+----------------+---------+-----------------------------+--------+-------------+
            

            Table Elimination does not eliminate product_article_id table, because it may have many matches.

            One can look at the query's select list

            SELECT COUNT(DISTINCT products.our_article_id) 
            

            and observe that

            • the query has implicit grouping
            • the aggregate function's arguments depend only on a column of products
            • the aggregate function is such that it doesn't matter its input has duplicate rows, so it doesn't matter if/how many matches are there in product_article_id.

            which allows to conclude that joining with product_article_id is unnecessary.

            The optimizer is not able to make this inference currently.

            psergei Sergei Petrunia added a comment - - edited The JOIN query is interesting: explain SELECT COUNT(DISTINCT our_article_id) FROM products LEFT JOIN product_article_id USING (our_article_id); -- 3.32s +------+-------------+--------------------+-------+----------------+----------------+---------+-----------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------------+-------+----------------+----------------+---------+-----------------------------+--------+-------------+ | 1 | SIMPLE | products | index | NULL | PRIMARY | 152 | NULL | 430681 | Using index | | 1 | SIMPLE | product_article_id | ref | our_article_id | our_article_id | 152 | j10.products.our_article_id | 399 | Using index | +------+-------------+--------------------+-------+----------------+----------------+---------+-----------------------------+--------+-------------+ Table Elimination does not eliminate product_article_id table, because it may have many matches. One can look at the query's select list SELECT COUNT ( DISTINCT products.our_article_id) and observe that the query has implicit grouping the aggregate function's arguments depend only on a column of products the aggregate function is such that it doesn't matter its input has duplicate rows, so it doesn't matter if/how many matches are there in product_article_id . which allows to conclude that joining with product_article_id is unnecessary. The optimizer is not able to make this inference currently.

            People

              psergei Sergei Petrunia
              puggan Puggan Se
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.