Den 20. apr. 2016 kl. 20.15 skrev Sergei Golubchik <serg@mariadb.org>:
On Apr 20, Erik Cederstrand wrote:
CREATE TABLE `t1` ( `i` int(11) NOT NULL, `j` int(11) NOT NULL, `l` int(11) NOT NULL, `m` int(11) DEFAULT NULL, PRIMARY KEY (`i`,`j`,`l`));
CREATE TABLE `t2` ( `i` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`i`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The problematic query is this: SELECT DISTINCT t2.i, t2.k, t1.j, t1.l, t1.m FROM t2 LEFT JOIN t1 ON t2.i=t1.i WHERE t2.k < 123;
The query is slow because the DISTINCT clause forces the query to use a temporary table. Removing DISTINCT makes the query 100x faster. ... it could also be argued that MariaDB should be smart enough to detect that the DISTINCT is unnecessary and ignore it (should I create a bug report for that?).
Yes, please, do report it! On the first glance this looks like a rather simple optimization.
Thanks. Reported as https://jira.mariadb.org/browse/MDEV-9964 Erik