[#MDEV-10922] optimization on count distinct on primary key

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?

[MDEV-10922] optimization on count distinct on primary key Created: 2016-09-29 Updated: 2023-11-24
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.0, 10.1, 10.2
Fix Version/s:	10.2

[MDEV-10922] optimization on count distinct on primary key Created: 2016-09-29 Updated: 2023-11-24