maria-discuss team mailing list archive

[Vassilis Virvilis <vasvir@xxxxxxxxxxxxxxxxx>]

Hi Sergei,

Thanks for the detailed and insightful answer.

I get it now. So I could invoke the index by FORCE INDEX. Interesting...

MDEV-8306 looks interesting!

The real problem I had is that I get correct results when index is utilized and wrong with filesort.

My table is more than 500M rows so I have no easy reproducer.

I had reported something similar here https://jira.mariadb.org/browse/MDEV-26552 but I don't know if this is related to the above behavior.

Anyway I will try to create a solid reproducer when I find some time.

Thanks again.

    Vassilis


On 10/12/21 7:32 PM, Sergei Golubchik wrote:
> 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