mySQL Query Optimization (RANGE)

Question

Ok so the query is dead simple. This is the table it is stored in:

CREATE TABLE `ips` (
  `ip` int(11) unsigned NOT NULL DEFAULT '0',
  `ip_start` int(11) unsigned NOT NULL DEFAULT '0',
  `ip_end` int(11) unsigned NOT NULL DEFAULT '0',
  KEY `IP` (`ip`),
  KEY `IP_RANGE_END` (`ip_end`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This table is filled with either singular IPs (in the IP column) or an IP Range (ip_start to ip_end).

Below is the function I am using:

CREATE DEFINER=`root`@`%` FUNCTION `in_adometry`(in_ip VARCHAR(30)) RETURNS int(11)
    DETERMINISTIC
BEGIN

SET @ip = 0;

SELECT COUNT(*) INTO @ip FROM ips WHERE (INET_ATON(in_ip) BETWEEN ip_start AND ip_end) OR  ip = INET_ATON(in_ip) LIMIT 1;

IF @ip > 0  THEN
    RETURN 1;
END IF;

RETURN 0;
END

I am currently hosting this on Amazon's RDS (medium size), and I can't get any more then 800 queries a second before the CPU hits over 100%. There is NOTHING else in this RDS but this one table. There are 1.1 million singular IPs, followed by about 3,000 IP Ranges (over 20 million IPs all together). What can I do to speed this up?

Gordon Linoff · Accepted Answer · 2014-02-14 03:01:30Z

1

Doing the count(*) is probably unnecessary. Try this:

BEGIN
IF exists (SELECT 1
           FROM ips
           WHERE INET_ATON(in_ip) BETWEEN ip_start AND ip_end OR ip = INET_ATON(in_ip)
          )
THEN
    RETURN 1;
END IF;

RETURN 0;

answered Feb 14, 2014 at 3:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RaviH · Accepted Answer · 2014-02-14 03:00:09Z

0

One possible reason for slow query is (from MySQL documentation):

With tables that use the MEMORY storage engine, if you run queries that examine ranges of values (using operators such as >, <=, or BETWEEN on the indexed columns), create the index with the USING BTREE clause. The default (USING HASH) is fast for retrieving individual rows with an equality operator (= or <=>), but is much slower (requiring a full table scan) to examine a range of column values. A MEMORY table created with the USING BTREE clause is still fast for equality comparisons, so use that clause for your MEMORY tables that handle a variety of queries.

Changing the index type can speed up your query dramatically. Please read Avoid Full Table Scan for more information.

answered Feb 14, 2014 at 3:00

RaviH

3,5942 gold badges17 silver badges14 bronze badges

3 Comments

George Milonas Over a year ago

Does this correlate to the fact that I am using an INNODB table though?

RaviH Over a year ago

Run EXPLAIN on your. That will clarify whether your query is doing full table scan.

George Milonas Over a year ago

Nope no full table scan, doing a union_join though on my two indexes.

Collectives™ on Stack Overflow

mySQL Query Optimization (RANGE)

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related