Re: Multi-column index issue?

[Felix Geerinckx]

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