Announcement

Announcement Module
Collapse
No announcement yet.

Why Index is not used?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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>

  • #2
    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.

    Comment

    Working...
    X