Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0, 10.1, 10.2
-
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
-
-
- Closed
-