StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If your table is <code>MyISAM</code>, you can improve this query using <code>SPATIAL</code> indexes:</p> <pre><code>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) </code></pre> <p>This article may be of interest to you:</p> <ul> <li><a href="http://explainextended.com/2009/04/04/banning-ips/" rel="nofollow"><strong>Banning IP's</strong></a></li> </ul> <p>Alternatively, if your ranges do not intersect (and from the nature of the database I except they don't), you can create a <code>UNIQUE</code> index on <code>geoip_base.start</code> and use this query:</p> <pre><code>SELECT * FROM geoip_base WHERE 1840344811 BETWEEN `start` AND `stop` ORDER BY `start` DESC LIMIT 1; </code></pre> <p>Note the <code>ORDER BY</code> and <code>LIMIT</code> conditions, they are important.</p> <p>This query is similar to this:</p> <pre><code>SELECT * FROM geoip_base WHERE `start` <= 1840344811 AND `stop` >= 1840344811 ORDER BY `start` DESC LIMIT 1; </code></pre> <p>Using <code>ORDER BY / LIMIT</code> makes the query to choose descending index scan on <code>start</code> which will stop on the first match (i. e. on the range with the <code>start</code> closest to the <code>IP</code> you enter). The additional filter on stop will just check whether the range contains this <code>IP</code>.</p> <p>Since your ranges do not intersect, either this range or no range at all will contain the <code>IP</code> you're after.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload