maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Erik!

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.

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx