Geek Answers Handbook

How to optimize this MySQL table for a single query

I have an InnoDB MySql Geo ID table that has ~ 1 million rows. The structure of the table is as follows:

CREATE TABLE `geoid` (
   `start_ip` int(11) NOT NULL,
   `end_ip` int(11) NOT NULL,
   `city` varchar(64) NOT NULL,
   `region` char(2) NOT NULL,
   PRIMARY KEY (`start_ip`,`end_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Only one query of the type will be executed in this table:

SELECT city, region FROM geoid WHERE 1259650516 BETWEEN start_ip AND end_ip

This request takes about ~ .4228 sec, which is not too slow but not incredibly fast broadcast.

My question is: how can I optimize my table for this single query?

I have tried the following things:

  • Change the storage mechanism to MyISAM, it made a request for about 1.9 s.
  • Use the WHERE clause 'WHERE geoid.start_ip <= 1259650516 AND 1259650516 <= geoid.end_ip'. But it will take about 0.5 seconds to execute instead of .4 ish.

I removed all useless rows from the table to reduce it. I need all 1 million lines.

UPDATE / DECISION

, , . ( - )

:

ALTER TABLE `geoid` ADD `geoip` LINESTRING NOT NULL

start_ip end_ip

GeomFromText(CONCAT('LINESTRING(', start_ip, ' -1, ', end_ip, ' 1)'))


CREATE SPATIAL INDEX geoip_index ON geoid(geoip);

, , :

SELECT city, region FROM geoid WHERE MBRContains(geoip, GeomFromText(CONCAT('POINT(', 1259650516, ' 0)')));

. 0,42 0,0003 .!!!!!!!

. . , .

+5
2

end_ip. .

SPATIAL, .

+3

, . (start_ip end_ip)

0

More articles:


All Articles