← Back to team overview

maria-discuss team mailing list archive

Re: MyISAM: single table GROUP BY plan changes based on LIMIT. BUG?

 

Hi, Vassilis!

On Oct 12, Vassilis Virvilis wrote:
> 
> I managed to create a trivial reproducer with a 10 rows table.
> 
> If I don't specify LIMIT the plan goes to filesort.
> If I specify LIMIT <= 9 the plan goes to utilize the index
> If I specify LIMIT >= 10 (table rows) the plan foes to filesort.
> 
> Is this behavior expected? Do you think I should report it?

Yes, expected. If you specify a LIMIT with more rows than what you have
in the table it's as if you didn't specify any limit at all. So the plan
is the same as with no LIMIT.

If you do specify a LIMIT (that actually limits the output) then
optimizer favors the index over filesort. It's a simple heuristics, it
assumes the limit is small and going through the index is much faster in
this case. If you specify a large limit this heuristics might be wrong.

A proper cost based approach was developed in
https://jira.mariadb.org/browse/MDEV-8306
But it's not in any release yet.

Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx


Follow ups

References