From: Michael Widenius
Date: June 5 2009 8:05pm
Subject: Re: Handling of keypart_map in custom storage engine
List-Archive: http://lists.mysql.com/internals/36850
Message-Id: <18985.31367.834957.925283@narttu.askmonty.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


Hi!

>>>>> "MARK" == MARK CALLAGHAN <mdcallag@stripped> writes:

<cut>

MARK> But speaking about index skip scan and bitmap indexes, when do we get
MARK> support for them? There is some support in the optimizer for index-or
MARK> and index-and. Is there any chance that will be expanded to support
MARK> more complex predicates and costs that make bitmap indexes
MARK> interesting?

Igor and I have several times talked about adding support for
non-prefix key scans.

For example:

SELECT * from t where key_part_2=5;

In the case that key_part1 has only a few different values, you can
solve the above query very fast by doing a 'read_after_key' and
jumping to key_part_2=5, scan over all values matched by this one and
then jump to the next key_part_1.

In other words, assuming the data:

key_part_1   key_part_2
1            1
1            2
1            5
1            6
1            7
2            1
2            2
2            5
2            5

MySQL would do:

Index_read_first  -> 1 1
Index_read (1,5)  -> 1 5
Index_read_next   -> 1 6
Index_read_after  -> 2 1
Index_read(2,5)   -> 2 5
Index_read_next   -> 2 5

etc

This wouldn't be that hard to implement and is on the todo for
MariaDB.

Regards,
Monty

For information of MariaDB, the community developed server based on
source code from MySQL, check out www.askmonty.org