Skip to main content

Documentation Index

Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt

Use this file to discover all available pages before exploring further.

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →
IP geolocation in MySQL works by importing a database that maps IP ranges to locations, then querying it with range checks. The standard approach stores IP ranges as integers and uses BETWEEN. VillageSQL’s INET type makes the schema more expressive and handles IPv4 and IPv6 in the same table.

​
How IP Geolocation Works

There’s no MySQL function that maps an IP to a country. Geolocation is a lookup problem: you have a table of IP ranges — each row says “addresses from X to Y are in country Z” — and you find which range contains the incoming IP. Free databases like MaxMind GeoLite2 provide these ranges as CSV files you import into MySQL.

​
The Standard Approach: Integer Ranges

MaxMind GeoLite2 (and similar databases) ship with network as a CIDR string and the start/end of each range as integers. The integer approach is compact and index-friendly: 
CREATE TABLE geoip_ranges (
    start_ip   INT UNSIGNED NOT NULL,
    end_ip     INT UNSIGNED NOT NULL,
    country    CHAR(2),
    INDEX idx_start (start_ip),
    INDEX idx_end   (end_ip)
);

-- Import from GeoLite2-Country-Blocks-IPv4.csv after converting to int ranges
-- Then query:
SELECT country FROM geoip_ranges
WHERE INET_ATON('203.0.113.45') BETWEEN start_ip AND end_ip
LIMIT 1;
This is fast — the index on start_ip enables efficient range scanning. The limitation is that it only works for IPv4. A separate table (with VARBINARY(16) ranges) is needed for IPv6.

​
With VillageSQL: Unified IPv4/IPv6 with INET

VillageSQL’s INET type stores both IPv4 and IPv6 addresses with natural ordering, so you can use a single table for both families. inet_compare() takes the place of BETWEEN for range checks. 
INSTALL EXTENSION vsql_network_address;

CREATE TABLE geoip_ranges (
    start_ip   INET NOT NULL,
    end_ip     INET NOT NULL,
    country    CHAR(2),
    region     VARCHAR(100),
    INDEX idx_start (start_ip)
);

-- Import IPv4 ranges
INSERT INTO geoip_ranges (start_ip, end_ip, country) VALUES
    (inet_from_string('203.0.113.0'), inet_from_string('203.0.113.255'), 'US'),
    (inet_from_string('198.51.100.0'), inet_from_string('198.51.100.255'), 'DE');

-- Import IPv6 ranges in the same table
INSERT INTO geoip_ranges (start_ip, end_ip, country) VALUES
    (inet_from_string('2001:db8::'), inet_from_string('2001:db8::ffff'), 'US');

-- Look up a country for an IPv4 address
SELECT country, region FROM geoip_ranges
WHERE inet_compare(inet_from_string('203.0.113.45'), start_ip) >= 0
  AND inet_compare(inet_from_string('203.0.113.45'), end_ip)   <= 0
LIMIT 1;

-- Same query works for IPv6
SELECT country FROM geoip_ranges
WHERE inet_compare(inet_from_string('2001:db8::42'), start_ip) >= 0
  AND inet_compare(inet_from_string('2001:db8::42'), end_ip)   <= 0
LIMIT 1;

​
Storing by CIDR network (alternative schema)

If your GeoIP data provides CIDR blocks rather than start/end pairs, use CIDR for the network column and resolve containment via subnet queries: 
CREATE TABLE geoip_cidrs (
    network    CIDR NOT NULL,
    country    CHAR(2),
    INDEX idx_network (network)
);

INSERT INTO geoip_cidrs (network, country) VALUES
    (cidr_from_string('203.0.113.0/24'), 'US'),
    (cidr_from_string('198.51.100.0/24'), 'DE');

-- Look up by subnet match
SELECT country FROM geoip_cidrs
WHERE cidr_to_string(inet_network(inet_set_masklen(
    inet_from_string('203.0.113.45'),
    inet_masklen(inet_from_string(cidr_to_string(network)))
))) = cidr_to_string(network)
LIMIT 1;

​
Choosing an Approach

ApproachIPv6SchemaPerformance
INT UNSIGNED range + BETWEENNo (separate table)Two separate IPv4/IPv6 tablesFast — integer index range scan
INET range + inet_compareYes (unified)Single table for bothGood — indexed on start_ip
CIDR network + subnet matchYes (unified)Single tableSlower — per-row network computation
For pure IPv4 and maximum query speed, the INT UNSIGNED + BETWEEN approach is hard to beat. For schemas that need to handle both IPv4 and IPv6 in one table, INET with inet_compare is cleaner and avoids maintaining two parallel tables.

​
Comparing Geolocation Data Sources

The query schema is only half the decision — the data source determines accuracy, freshness, and cost. Here’s how the common options compare:
Data SourceCountry accuracyCity accuracyLatencyCostMySQL integration
MaxMind GeoLite2 (free)~98%~80%In-process (SQL or .mmdb)Free, registration requiredImport CSV; update weekly
MaxMind GeoIP2 (paid)~99.8%~85–90%In-processFrom $20/moSame import flow as GeoLite2
ip-api.com (free tier)~99%~83%HTTP, ~30–100 msFree (45 req/min); paid removes limitApplication-layer HTTP call; results stored in MySQL
ip-api.com (pro)~99%~83%HTTP, ~30–100 msFrom $15/moSame; HTTPS + no rate limit
ipinfo.io~99%~85%HTTP, ~30–100 msFree (50k req/mo); paid tiers aboveApplication-layer; results can be cached in MySQL
Country-level accuracy figures are self-reported by providers and vary by region — Asia-Pacific and Latin American coverage tends to be lower than North America and Europe across all sources. Which to pick: MaxMind GeoLite2 is the standard starting point — it’s free, ships as flat files you import once, and lookups run entirely inside MySQL with no external dependency. For city-level accuracy in production, GeoIP2 Precision is the upgrade path. API-based sources (ip-api.com, ipinfo.io) are convenient for low-volume use but add HTTP latency and a rate-limit dependency to every lookup.

​
When to Use Each Approach

ScenarioRecommended approach
Batch analytics on stored access logsGeoLite2 in MySQL — join directly in SQL
Real-time lookup on every HTTP request (high volume)MaxMind .mmdb reader in application code — no SQL round-trip
Low-volume enrichment (<45 req/min), no DB import overheadip-api.com free tier via HTTP
IPv4 only, maximum query speedINT UNSIGNED + BETWEEN with GeoLite2
IPv4 + IPv6 in a single tableVillageSQL INET + inet_compare with GeoLite2
City, ASN, or ISP data neededGeoIP2 Precision (paid) or ipinfo.io
Air-gapped environment, no external API callsMaxMind GeoLite2 (local import)
Prototyping / testing with no setupip-api.com free tier

​
Importing GeoLite2 Data

MaxMind GeoLite2 (free, requires registration) provides CSV files. For the range-based schema: 
-- After downloading GeoLite2-Country-Blocks-IPv4.csv
-- Convert the network column to start/end ranges and load:
LOAD DATA INFILE '/path/to/ipv4_ranges.csv'
INTO TABLE geoip_ranges
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(start_ip_str, end_ip_str, country)
SET start_ip = inet_from_string(start_ip_str),
    end_ip   = inet_from_string(end_ip_str);
MaxMind also provides a binary .mmdb format and official client libraries for languages like Python, Node.js, and Go that are faster than SQL lookups for high-volume scenarios.

​
Frequently Asked Questions

​
Should I do geolocation in MySQL or in application code?

For high-volume lookups (every HTTP request), application-layer geolocation using MaxMind’s .mmdb reader library is faster — binary search in memory, no SQL round-trip. SQL-based geolocation makes sense for batch analytics, reporting queries, and data pipelines that already run in MySQL.

​
How do I keep the GeoIP database current?

MaxMind updates GeoLite2 weekly. Script a periodic download and TRUNCATE + reload of the geolocation table. Alternatively, use MaxMind’s mmdbinspect or a refresh script to apply delta updates.

​
How accurate is IP geolocation in MySQL?

Accuracy depends entirely on the data source, not the MySQL schema. At the country level, MaxMind GeoLite2 (free) is accurate for roughly 98% of IPv4 addresses. At the city level it drops to around 80%. For higher accuracy, MaxMind’s paid GeoIP2 Precision product reaches ~99.8% country and ~85–90% city. No database is perfect — accuracy is typically lower for mobile networks, VPNs, and some Asia-Pacific ranges. The MySQL query approach has no effect on accuracy; you get exactly what the imported data says.

​
Can MySQL look up IP geolocation without a plugin or external API?

Yes. The standard approach — and what this guide covers — uses a GeoIP database (like MaxMind GeoLite2) imported into a regular MySQL table as integer ranges. Lookups are plain SQL BETWEEN queries. No plugin, no external API, no network call at query time. The only dependency is importing and periodically refreshing the flat CSV files from MaxMind. VillageSQL’s vsql_network_address extension adds an INET type for cleaner IPv4/IPv6 handling, but it’s not required for the basic approach.

​
Can I join geolocation with my access logs in one query?

Yes — that’s one of the main reasons to keep GeoIP data in MySQL: 
SELECT
    inet_to_string(a.ip_address) AS ip,
    g.country,
    COUNT(*) AS request_count
FROM access_log a
JOIN geoip_ranges g
  ON inet_compare(a.ip_address, g.start_ip) >= 0
 AND inet_compare(a.ip_address, g.end_ip)   <= 0
GROUP BY g.country
ORDER BY request_count DESC;

​
Troubleshooting

ProblemSolution
FUNCTION inet_from_string does not existRun INSTALL EXTENSION vsql_network_address
Lookup returns no resultsCheck that the IP range for that block was imported; try SELECT COUNT(*) FROM geoip_ranges
Lookup returns wrong countryGeoIP databases have coverage gaps — verify against MaxMind’s web lookup tool
LOAD DATA INFILE failsCheck secure_file_priv setting; alternatively use application-side batch inserts

​
See also