MySQL Forums
Forum List  »  Performance

Re: Multi-column index issue?
Posted by: Felix Geerinckx
Date: April 27, 2005 06:39AM

Marc Gregoire wrote:


> I'm having a problem with a multi-column index.

> Why isn't it using the index? How can I optimize this query? What is the fastest way to do such a
> range query?

With

CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ip1 INT(10) NOT NULL,
ip2 INT(10) NOT NULL,
INDEX ip1 (ip1),
INDEX ip2 (ip2)
) ENGINE=MyISAM;

and 10000 random records, I get for

mysql> explain select id from foo where ip1 < 16123123 and ip2 > 16123123;

the expected result:

+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | foo | range | ip1,ip2 | ip1 | 4 | NULL | 1182 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

--
felix

Options: ReplyQuote


Subject
Views
Written By
Posted
3145
April 27, 2005 06:21AM
Re: Multi-column index issue?
1988
April 27, 2005 06:39AM
2192
April 27, 2005 06:47AM
1941
April 27, 2005 07:19AM
2114
April 27, 2005 07:34AM
3349
April 27, 2005 11:47AM


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.