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>