Mysql – performance issue on using ip2location for group of ips

MySQLperformance

In admin panel I want to show counting users by country. I have in database these tables 

CREATE TABLE `cities` (
  `id` int(11) UNSIGNED NOT NULL,
  `country_id` int(11) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `country` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ips` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ip_city` (
  `id` int(10) UNSIGNED NOT NULL,
  `ip_from` int(10) UNSIGNED DEFAULT NULL,
  `ip_to` int(10) UNSIGNED DEFAULT NULL,
  `city_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `country_fk` (`country_id`);

ALTER TABLE `country`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `ips`
  ADD PRIMARY KEY (`id`),
  ADD KEY `ips_user_id_foreign` (`user_id`);

ALTER TABLE `ip_city`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `city_index` (`id`),
  ADD KEY `idx_ip_from` (`ip_from`),
  ADD KEY `idx_ip_to` (`ip_to`),
  ADD KEY `idx_ip_from_to` (`ip_from`,`ip_to`),
  ADD KEY `city_index_fk` (`city_id`);


ALTER TABLE `cities`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `country`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `ips`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `ip_city`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;


ALTER TABLE `cities`
  ADD CONSTRAINT `country_fk` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ips`
  ADD CONSTRAINT `ips_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ip_city`
  ADD CONSTRAINT `city_index_fk` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I write this query:

select 
country.name,
count(*)

from (
    /*get last ips */
SELECT
    INET_ATON(m1.ip) AS ip
FROM
    ips m1
LEFT JOIN ips m2 ON
    (
        m1.user_id = m2.user_id AND m1.id < m2.id
    )
WHERE
    m2.id IS NULL
    ) a
/*end of getting users ips (around 3500) */ 
inner JOIN ip_city

on ip between `ip_from` and `ip_to`



inner join cities on ip_city.city_id = cities.id
inner JOIN country on  cities.country_id = country.id

group by country.id

but it very slow.
Problem is in :

inner JOIN ip_city

on ip between `ip_from` and `ip_to`

for every ip it consuming around 1 second.
I found this query 

SELECT
    *
FROM
    (
    SELECT
        *
    FROM
        ip_city
    WHERE
        ip_to >= INET_ATON('8.8.8.8')
    LIMIT 1
) AS tmp
WHERE
    ip_from <= INET_ATON('8.8.8.8')

It work very fast but it work only for one ip.
How to combine these 2 querys or how to optimize first query ?

Best Answer

Please qualify all columns with what table they are in. Meanwhile, I will guess that ip_from and ip_to are in ip_city.

A problem is that the tables are over-normalized. ip_city -> cities -> country instead of having a single table slows down the query.

But the main problem is that there is no good index for ip between ip_from AND ip_to. Any index with ip_from and/or ip_to can, at best, limit the effort down to scanning half the table. That's O(N). To get to O(1), see http://mysql.rjweb.org/doc.php/ipranges . Sorry, it is rather involved.

A first cut at improving your existing schema:

CREATE TABLE `cities` (
  `id` MEDIUMINT UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `country_code` CHAR(2) CHARACTER SET ascii NOT NULL,
  `latitude` FLOAT DEFAULT NULL,
  `longitude` FLOAT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ips` (
  `ip` varchar(39) CHARACTER SET ascii NOT NULL,
  ip_packed BINARY(16) NOT NULL
  `user_id` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL
  PRIMARY KEY(ip)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

CREATE TABLE `ip_city` (
  `ip_from` BINARY(16) DEFAULT NULL,
  `ip_to` BINARY(16) DEFAULT NULL,
  `city_id` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY(ip_from, ip_to),
  INDEX(city_id)  -- if desired
) ENGINE=InnoDB DEFAULT;

That included getting rid if id when there were good 'natural' PKs, handling IPv6, using standard 2-letter country_codes, tossing the country table, using ascii where appropriate, etc.

See my link for further discussion, including why FLOAT is better.

Related Question