MARIADB: Index not used for a select with join on a range

Question

I have a first table containing my ips stored as integer (500k rows), and a second one containing ranges of black listed ips and the reason of black listing (10M rows) here is the table structure :

    CREATE TABLE `black_lists` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `ip_start` INT(11) UNSIGNED NOT NULL,
    `ip_end` INT(11) UNSIGNED NULL DEFAULT NULL,
    `reason` VARCHAR(3) NOT NULL,
    `excluded` TINYINT(1) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `ip_range` (`ip_end`, `ip_start`),
    INDEX `ip_start` ( `ip_start`),
    INDEX `ip_end` (`ip_end`),
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10747741
;

CREATE TABLE `ips` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Id ips',
    `idhost` INT(11) NOT NULL COMMENT 'Id Host',
    `ip` VARCHAR(45) NULL DEFAULT NULL COMMENT 'Ip',
    `ipint` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'Int ip',
    `type` VARCHAR(45) NULL DEFAULT NULL COMMENT 'Type',
    PRIMARY KEY (`id`),
    INDEX `host` (`idhost`),
    INDEX `index3` (`ip`),
    INDEX `index4` (`idhost`, `ip`),
    INDEX `ipsin` (`ipint`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=675651;

my problem is when I try to run this query no index is used and it takes an eternity to finish :

select i.ip,s1.reason
from ips i
    left join black_lists s1 on i.ipint BETWEEN s1.ip_start and s1.ip_end;

I'm using MariaDB 10.0.16

If you wrote a query that has at least some actual information where to start digging through data, then indexes would be used. Why don't you spend 20 seconds thinking about what you did before asking a question such as this one? Also, do you know what index really is? It's not a black magic thing that makes queries fast out of the blue. I suggest that you dig up some info on what indexes are and how they work before asking for help with a query that can't use indexes, even if you got a wish granted by a fairy - she couldn't make this query to use indexes. — N.B.
– N.B., Commented Jul 14, 2016 at 21:49
I asked this question because this query used indexes: "select s1.reason from black_lists s1 on 111111111 BETWEEN s1.ip_start and s1.ip_end;", so I supposed if I did a left join it would be like a loop on this one — elarichi.y
– elarichi.y, Commented Jul 15, 2016 at 10:28
But you supplied a value here so MySQL knows where to start looking. If you don't supply an actual value (number such as 111111111) then what can it do except look at everything and spit out a huge amount of records? — N.B.
– N.B., Commented Jul 15, 2016 at 15:57

Rick James · Accepted Answer · 2016-07-14 21:23:59Z

True.

The optimizer has no knowledge that start..end values are non overlapping, nor anything else obvious about them. So, the best it can do is decide between

s1.ip_start <= i.ipint  -- and use INDEX(ip_start), or
s1.ip_end   >= i.ipint  -- and use INDEX(ip_end)

Either of those could result in upwards of half the table being scanned.

In 2 steps you could achieve the desired goal for one ip; let's say @ip:

SELECT ip_start, reason
    FROM black_lists
    WHERE ip_start <= @ip
    ORDER BY ip_start DESC
    LIMIT 1

But after that, you need to see if the ip_end corresponding to that ip_start is <= @ip before deciding whether you have a black-listed item.

SELECT reason
    FROM ( ... ) a   -- fill in the above query
    JOIN black_lists b  USING(ip_start)
    WHERE b.ip_end <= @ip

That will either return the reason or no rows.

In spite of the complexity, it will be very fast. But, you seem to have a set of IPs to check. That makes it more complex.

For black_lists, there seems to be no need for id. Suggest you replace the 4 indexes with only 2:

PRIMARY KEY(ip_start, ip_end),
INDEX(ip_end)

In ips, isn't ip unique? If so, get rid if id and change 5 indexes to 3:

PRIMARY KEY(idint),
INDEX(host, ip),
INDEX(ip)

You have allowed more than enough in the VARCHAR for IPv6, but not in INT UNSIGNED.

More discussion.

Collectives™ on Stack Overflow

MARIADB: Index not used for a select with join on a range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related