Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0(EOL), 10.1(EOL), 10.2(EOL)
-
Package: mariadb-server
Version: 10.0.27-0ubuntu0.16.04.1
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
- is duplicated by
-
MDEV-30660 Aggregation functions fail to leverage uniqueness property
-
- Closed
-
Activity
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)
|
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)
|
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.
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.
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.