> ## 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.

# Geolocation Lookups by IP in MySQL

> How to do IP geolocation lookups in MySQL — importing MaxMind GeoLite2 or an API-based data source, querying IP ranges for country and region, accuracy trade-offs, and using VillageSQL's INET type for unified IPv4/IPv6 lookups with sub-millisecond latency.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/mysql-8.4/0.0.4/quickstart">
  All examples in this guide work on VillageSQL. Install Now →
</Card>

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:

```sql theme={null}
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.

```sql theme={null}
INSTALL EXTENSION vsql_network_address;
```

```sql theme={null}
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:

```sql theme={null}
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

| Approach                         | IPv6                | Schema                        | Performance                          |
| :------------------------------- | :------------------ | :---------------------------- | :----------------------------------- |
| `INT UNSIGNED` range + `BETWEEN` | No (separate table) | Two separate IPv4/IPv6 tables | Fast — integer index range scan      |
| `INET` range + `inet_compare`    | Yes (unified)       | Single table for both         | Good — indexed on `start_ip`         |
| `CIDR` network + subnet match    | Yes (unified)       | Single table                  | Slower — 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 Source             | Country accuracy | City accuracy | Latency                     | Cost                                  | MySQL integration                                    |
| :---------------------- | :--------------- | :------------ | :-------------------------- | :------------------------------------ | :--------------------------------------------------- |
| MaxMind GeoLite2 (free) | \~98%            | \~80%         | In-process (SQL or `.mmdb`) | Free, registration required           | Import CSV; update weekly                            |
| MaxMind GeoIP2 (paid)   | \~99.8%          | \~85–90%      | In-process                  | From \$20/mo                          | Same import flow as GeoLite2                         |
| ip-api.com (free tier)  | \~99%            | \~83%         | HTTP, \~30–100 ms           | Free (45 req/min); paid removes limit | Application-layer HTTP call; results stored in MySQL |
| ip-api.com (pro)        | \~99%            | \~83%         | HTTP, \~30–100 ms           | From \$15/mo                          | Same; HTTPS + no rate limit                          |
| ipinfo.io               | \~99%            | \~85%         | HTTP, \~30–100 ms           | Free (50k req/mo); paid tiers above   | Application-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

| Scenario                                                    | Recommended approach                                           |
| :---------------------------------------------------------- | :------------------------------------------------------------- |
| Batch analytics on stored access logs                       | GeoLite2 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 overhead | ip-api.com free tier via HTTP                                  |
| IPv4 only, maximum query speed                              | `INT UNSIGNED` + `BETWEEN` with GeoLite2                       |
| IPv4 + IPv6 in a single table                               | VillageSQL `INET` + `inet_compare` with GeoLite2               |
| City, ASN, or ISP data needed                               | GeoIP2 Precision (paid) or ipinfo.io                           |
| Air-gapped environment, no external API calls               | MaxMind GeoLite2 (local import)                                |
| Prototyping / testing with no setup                         | ip-api.com free tier                                           |

## Importing GeoLite2 Data

MaxMind GeoLite2 (free, requires registration) provides CSV files. For the range-based schema:

```sql theme={null}
-- 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:

```sql theme={null}
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

| Problem                                    | Solution                                                                                     |
| :----------------------------------------- | :------------------------------------------------------------------------------------------- |
| `FUNCTION inet_from_string does not exist` | Run `INSTALL EXTENSION vsql_network_address`                                                 |
| Lookup returns no results                  | Check that the IP range for that block was imported; try `SELECT COUNT(*) FROM geoip_ranges` |
| Lookup returns wrong country               | GeoIP databases have coverage gaps — verify against MaxMind's web lookup tool                |
| `LOAD DATA INFILE` fails                   | Check `secure_file_priv` setting; alternatively use application-side batch inserts           |

## See also

* [Storing IP Addresses in MySQL](/guides/storing-ip-addresses) — efficient IP storage before geolocation queries
* [Querying by Subnet in MySQL](/guides/subnet-queries) — range-based IP lookups that geolocation builds on