From: Jay Pipes
Date: August 29 2007 7:50pm
Subject: Re: what does "index map: N" mean?
List-Archive: http://lists.mysql.com/internals/34973
Message-Id: <46D5CE0C.6000808@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

This is very interesting.  Sergey, is this engine-agnostic?  I know that 
Falcon has sparse bitmap indexing (already?) but I did not know if other 
engines support the concept (if not, are we "emulating" it in the 
optimizer?)

Baron, which engine are you testing against?

On a related note, I have been testing performance regressions of 
subqueries in 5.2.6 (yes, I wrote regressions, not improvements...) and 
have noticed another EXPLAIN phrase:

"Using index condition"

Sergey, what does this mean?

For reference, see the bug report I filed here:

http://bugs.mysql.com/?id=30659

Thanks!

Jay

Baron Schwartz wrote:
> Hi,
> 
> Sergey Petrunia wrote:
>> Hi!
>>
>> On 8/29/07, Baron Schwartz <baron@stripped> wrote:
>>> Sometimes in EXPLAIN output, I see this in the Extra field:
>> ...
>>> explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 7 and
>>> t1.y+0;
>>> id    select_type    table    type    possible_keys    key    
>>> key_len    ref    rows    Extra
>>> 1    SIMPLE    t1    ref    y    y    5    const    1    Using where
>>> 1    SIMPLE    t2    range    x    x    5    NULL    4    Range 
>>> checked for each record (index map: 0x1)
>>>
>>> I know what the rest of it means, but what does 0x1 mean?
>>
>> It is a bitmap of indexes which can be used by range/index_merge
>> access that is performed for each row combination of the preceding
>> tables.
> 
> Thanks Sergey, Chad.  Follow-up question: can I assume that in the 
> output of SHOW CREATE TABLE, 0x1 is the first index, 0x2 is the second, 
> 0x3 is the first and second -- in the order they appear in that output? 
>  If not, how would I be able to tell which indexes the bitmap refers to?
> 
> Thanks
> Baron
>