MySQL Forums
Forum List  »  InnoDB

Re: the where condition field after ">=" can use index? is this true?
Posted by: Rick James
Date: August 22, 2015 02:00PM

My ego thanks you for the compliment.

16 is strange.

Each INT NOT NULL should be 4 bytes toward Key_len. (NULL would make it 5.)

`idx_ac` is effectively (`aaa`,`ccc`, id), which is 3 INT NOT NULL, hence Key_len can't be 16.

"Using index condition" is also called "Index Condition Pushdown" (ICP), if you want to search for what it means. It's a minor optimization in newer versions of MySQL and MariaDB.
5.6.2: http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

Perhaps a typo? Notice the type discrepancy between these:
`ccc` int(11)
ccc > 'abc'

Another note... It is hard to tell from the regular EXPLAIN whether it is using only one range or both. Try EXPLAIN FORMAT=JSON SELECT...; That may be clearer in this detail.

By doing this, you can sort of see how many index rows it touches to perform the query:

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

I would expect to see a number close to
( SELECT COUNT(*) .. aaa >= 123 ),
and not the smaller number of
( SELECT COUNT(*) .. aaa >= 123 and ccc > 'abc' )

If so, that would further 'prove' that only `aaa` was used in the index.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: the where condition field after ">=" can use index? is this true?
844
August 22, 2015 02:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.