MySQL Forums
Forum List  »  Optimizer & Parser

Re: Two columns indexes
Posted by: Rick James
Date: March 22, 2011 07:54AM

That is a very difficult query to optimize. A partial answer is

SELECT a.* 
    FROM mapping AS a
    JOIN (
        SELECT id
            FROM mapping 
            WHERE ip_from < 999617043
              AND ip_to   > 999617043
         ) AS b  ON a.id = b.id; 
together with these
PRIMARY KEY(id)
INDEX(ip_from, ip_to, id)
If you don't already have PRIMARY KEY(id), please
SHOW CREATE TABLE mapping
so I can advise you on an alternative.

Once you do EXPLAIN SELECT ..., you will see "Using index" in my version. In cases where the number of rows is significantly less than the total table, this will run faster. If the query delivers most of the rows, my version may be slower.

If ip_from is an IP-address, then it must be only IPv4. IPv6 is coming now! DOUBLE will not be big enough. I recommend BINARY(16) (for binary representation) or BINARY(39) (for '1234:0000:...' representation). Either of those will work correctly with "<".

Options: ReplyQuote


Subject
Views
Written By
Posted
2044
March 20, 2011 10:37PM
Re: Two columns indexes
1176
March 22, 2011 07:54AM


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.