L14ddWLS

1mysql select between two columns works too slowly
2SELECT `country`
3FROM `geoip_base`
4WHERE 1840344811 BETWEEN `start` AND `stop`
5	
6CREATE TABLE IF NOT EXISTS `geoip_base` (
7  `start` decimal(10,0) NOT NULL,
8  `stop` decimal(10,0) NOT NULL,
9  `inetnum` char(33) collate utf8_bin NOT NULL,
10  `country` char(2) collate utf8_bin NOT NULL,
11  `city_id` int(11) NOT NULL,
12  PRIMARY KEY  (`start`,`stop`),
13  UNIQUE KEY `start` (`start`),
14  UNIQUE KEY `stop` (`stop`)
15) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
16	
17ALTER TABLE
18        geoip_base
19ADD     ip_range LineString;
20
21UPDATE  geoip_base
22SET     ip_range =
23        LineString
24                (
25                Point(-1, `start`),
26                Point(1, `stop`)
27                );
28
29ALTER TABLE
30        geoip_base
31MODIFY  ip_range NOT NULL;
32
33CREATE SPATIAL INDEX
34        sx_geoip_range ON geoip_base (ip_range);
35
36SELECT  country
37FROM    geoip_base
38WHERE   MBRContains(ip_range, Point(0, 1840344811)
39	
40SELECT  *
41FROM    geoip_base
42WHERE   1840344811 BETWEEN `start` AND `stop`
43ORDER BY
44        `start` DESC
45LIMIT 1;
46	
47SELECT  *
48FROM    geoip_base
49WHERE   `start` <= 1840344811
50        AND `stop` >= 1840344811
51ORDER BY
52        `start` DESC
53LIMIT 1;
54	
55SELECT `country` FROM table WHERE 1500 BETWEEN `start` AND `stop` AND start >= 1500