Why Index is not used?

Hi,

I am trying compare IP address with Netmask filtering , somehow MySQL is not using Index. Anyone can tell me why? is there any better way to achieve same logic? Thanks in Adance

-Naush.

=========here is sample test.sql===========================
drop table if exists Carriers;
create table Carriers
(
CarrierId int(11) DEFAULT NULL,
IPn int(10) unsigned DEFAULT NULL, /* 4 byte IP address /
NMn int(10) unsigned DEFAULT NULL, /
4 byte Net mask */
PRIMARY KEY (CarrierId),
UNIQUE KEY IDX_IPn (IPn)
) ENGINE=MyISAM;

Insert into Carriers (CarrierId,IPn,NMn) values(1,INET_ATON(“1.2.3.4”), INET_ATON(“255.255.255.0”));

==============end of test.sql =================================================

mysql> explain select CarrierId from Carriers where IPn = INET_ATON(“1.2.3.4”) & NMn \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL <— Why is it not using IDX_IPn??
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

mysql>

Explain isn’t bothering to give you the usual amount of information because it can tell your query will return an empty set. That’s what the “impossible WHERE” is all about. You’ll get the same output if you do something that is logically an empty set:

explain select * from Carriers where IPn != IPn

MySQL will use indexes only when it believes it will be faster than a table scan, which is only the case when your result set is significantly smaller than the number of rows in the table. So even if you had changed your example to lookup exactly what you inserted:

explain select * from Carriers where IPn = INET_ATON(“1.2.3.4”)

it wouldn’t use an index because it would be less efficient, since you’re asking it to read 100% of the table (which is just one row) to satisfy your query.