maria-discuss team mailing list archive

[Erik Cederstrand <erik@xxxxxxxxxxxxxx>]

> Den 20. apr. 2016 kl. 20.15 skrev Sergei Golubchik <serg@xxxxxxxxxxx>:
> 
> 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

Attachment: signature.asc
Description: Message signed with OpenPGP using GPGMail