Search code examples
mysqlperformancedelay

MySQL compare query performance

I have a table in MySQL like this:

startIp endIp city countryCode latitude longitude
16777216 16777471 Los Angeles US 34.0522 -118.244
16777472 16778239 Fuzhou CN 26.0614 119.306
16778240 16779263 Melbourne AU -37.814 144.963

and 2.7 million more entries.

Now I have a converted IP Adresss like 16777566.
This should return "Fuzhou, CN, 26.0614, 119.306"

Right now I use this query: SELECT * FROM kombiniert WHERE startIp < 16777566 AND endIp > 16777566
It works really well but its to slow.

Performance:

Without LIMIT: SELECT * FROM kombiniert WHERE startIp < 2264918979 AND endIp > 2264918979; avg (2300ms)
With LIMIT: SELECT * FROM kombiniert WHERE startIp < 2264918979 AND endIp > 2264918979 LIMIT 1; avg (1500ms)
Indexed Without LIMIT: SELECT * FROM kombiniert WHERE startIp < 2264918979 AND endIp > 2264918979; avg (5300ms)
Indexed With LIMIT: SELECT * FROM kombiniert WHERE startIp < 2264918979 AND endIp > 2264918979 LIMIT 1; avg (5500ms)


Now I want to speed up this query! Why should I do?
Thanks so much!


EDIT:I forgot to mention: The fields startIp, endIp are bigint!
EDIT2: table creation sql:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `kombiniert` (<br>
  `id` int(11) NOT NULL,<br>
  `startIp` bigint(20) NOT NULL,<br>
  `endIp` bigint(20) NOT NULL,<br>
  `city` text NOT NULL,<br>
  `countryCode` varchar(4) NOT NULL,<br>
  `latitude` float NOT NULL,<br>
  `longitude` float NOT NULL<br>
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;<br>
<br>
ALTER TABLE `kombiniert`<br>
  ADD PRIMARY KEY (`id`),<br>
  ADD KEY `startIp` (`startIp`),<br>
  ADD KEY `endIp` (`endIp`);<br>
<br>
ALTER TABLE `kombiniert`<br>
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2747683;<br>
COMMIT;<br>
Solution

  • Searching for IP addresses (or any other metric that is split into buckets) is not efficient. Or at least not efficient with the obvious code. The best average performance you can get is scanning one-quarter of the table for what you are looking for. That is "Order(N)".

    You can get "Order(1)" performance for most operations, but it takes a restructuring of the table and the query. See http://mysql.rjweb.org/doc.php/ipranges