← Back to team overview

maria-developers team mailing list archive

Re: Performance of index intersection



On Wed, Oct 02, 2019 at 06:55:55PM +1300, David Sickmiller wrote:
> Last weekend I started playing with the code, but it looks like I may need
> to upgrade to an SSD to get a reasonable build time.

Did you try -jN argument to make, where N is about 2x number of cpus..

> For Zone B I was imagining there would be a way to try increasingly large
> jumps, perhaps aided by an index cursor that remembered some information it
> saw as it walked through the B*-tree and page directory.

I'm not sure if I follow this idea...

I was thinking of something like a forward scan that would continue forward
until the end of the prefetch buffer, the end of the page, etc. This way, any
advantages of a continuous scan will still be there.
But as soon as we need to jump forward, we will use the new index lookup value.

> I'm happy to hear an optimization to skip over Zone A looks fairly easy.

Ok, I've filed an MDEV for it, and have put in some details:

> As for real data, do full-text indexes return rowid-ordered rows? If so,
> queries for a long-tail word (e.g. "hobbit") AND a frequent value (e.g.
> soft_deleted=0) should benefit significantly from skipping over Zone A.
> Maybe we could use a Wikipedia dump?

The issue with this is that fulltext scan cannot be a part of index_merge.
But perhaps it would be possible to find a non-full-text condition.

Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog