MySQL compare query performance

I have a table in MySQL like this:

startIpendIpcitycountryCodelatitudelongitude
1677721616777471Los AngelesUS34.0522-118.244
1677747216778239FuzhouCN26.0614119.306
1677824016779263MelbourneAU-37.814144.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>

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x