mysqlselectindexingbetween

mysql select between two columns works too slowly


I have this query:

SELECT `country`
FROM `geoip_base`
WHERE 1840344811 BETWEEN `start` AND `stop`

It's badly use index (use, but parse big part of table) and work too slowly. I tried use ORDER BY and LIMIT, but it hasn't helped.

"start <= 1840344811 AND 1840344811 <= stop" works similar.

CREATE TABLE IF NOT EXISTS `geoip_base` (
  `start` decimal(10,0) NOT NULL,
  `stop` decimal(10,0) NOT NULL,
  `inetnum` char(33) collate utf8_bin NOT NULL,
  `country` char(2) collate utf8_bin NOT NULL,
  `city_id` int(11) NOT NULL,
  PRIMARY KEY  (`start`,`stop`),
  UNIQUE KEY `start` (`start`),
  UNIQUE KEY `stop` (`stop`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Table have 57,424 rows.

Explain for query "... BETWEEN START AND STOP ORDER BY START LIMIT 1": using key stop and get 24099 rows. Without order and limit, mysql doesn't use keys and gets all rows.


Solution

  • If your table is MyISAM, you can improve this query using SPATIAL indexes:

    ALTER TABLE
            geoip_base
    ADD     ip_range LineString;
    
    UPDATE  geoip_base
    SET     ip_range =
            LineString
                    (
                    Point(-1, `start`),
                    Point(1, `stop`)
                    );
    
    ALTER TABLE
            geoip_base
    MODIFY  ip_range NOT NULL;
    
    CREATE SPATIAL INDEX
            sx_geoip_range ON geoip_base (ip_range);
    
    SELECT  country
    FROM    geoip_base
    WHERE   MBRContains(ip_range, Point(0, 1840344811)
    

    This article may be of interest to you:

    Alternatively, if your ranges do not intersect (and from the nature of the database I except they don't), you can create a UNIQUE index on geoip_base.start and use this query:

    SELECT  *
    FROM    geoip_base
    WHERE   1840344811 BETWEEN `start` AND `stop`
    ORDER BY
            `start` DESC
    LIMIT 1;
    

    Note the ORDER BY and LIMIT conditions, they are important.

    This query is similar to this:

    SELECT  *
    FROM    geoip_base
    WHERE   `start` <= 1840344811
            AND `stop` >= 1840344811
    ORDER BY
            `start` DESC
    LIMIT 1;
    

    Using ORDER BY / LIMIT makes the query to choose descending index scan on start which will stop on the first match (i. e. on the range with the start closest to the IP you enter). The additional filter on stop will just check whether the range contains this IP.

    Since your ranges do not intersect, either this range or no range at all will contain the IP you're after.