MySQL Forums
Forum List  »  InnoDB

Re: the where condition field after ">=" can use index? is this true?
Posted by: Bing Wu
Date: August 19, 2015 08:24PM

Hi, James. Thank you for your direction. I've read your blog. It's perfact.
I have a question about "Algorithm, Step 2a (one range)".
Your example is
"WHERE aaa >= 123 AND ccc > 'xyz' ⇒ INDEX(aaa) or INDEX(ccc) (only one range)".
I have tried it out:
First, I created a talbe like this:
`id` int(11) NOT NULL AUTO_INCREMENT,
`aaa` int(11) NOT NULL DEFAULT '0',
`ccc` int(11) NOT NULL DEFAULT '0',
`xxx` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_ac` (`aaa`,`ccc`)

Then, I ran the sql to see use of index idx_ac:
explain select * from T force index (idx_ac) where aaa >= 123 and ccc > 'abc';

As you directed, I have expected the "key_len" is 4 (int is 4-byte), but in fact, the key_len is 16. I'm confused. Has MySQL used the full index(idx_ac) ? Or, is there any bug in explain ?
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------+
| 1 | SIMPLE | T | range | idx_ac | idx_ac | 16 | NULL | 1002 | Using index condition |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: the where condition field after ">=" can use index? is this true?
758
August 19, 2015 08:24PM


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.