4 ms·

Yeah so he reduced the number of database rows from 3M to 1.4M. But you shouldn't be using a normal database for this type of query anyways - it's SLOW, because

by schammy 18y ago

Yeah so he reduced the number of database rows from 3M to 1.4M. But you shouldn't be using a normal database for this type of query anyways - it's SLOW, because you're doing a range search. Maxmind also offers a binary database that is extremely fast, even with just a PHP script that they provide that parses through it - and I guarantee you this is at least 10 times as fast as doing it with MySQL. And if you install their Apache module, which is not hard (although you do need root access on the machine), it's something like 20x to 30x faster. Not to put down this person's/people's work, but don't do IP geolocation with MySQL if performance is of any importance to you. Use the binary database that Maxmind provides and you can get much better performance with almost no extra effort.

9oliYQjP 18y ago
You bring up a good point, but distributing this as a database is a convenient interchange format. It means I can quickly start playing around with it to see how accurate and precise it is. I downloaded it, imported it into a test database, and was querying it in under 2 minutes. It took me longer than that just to figure out what sort of license I would need to use Maxmind.
trezor 18y ago
But you shouldn't be using a normal database for this type of query anyways - it's SLOW, because you're doing a range search Please explain to me why databases is a bad choice for data which you wish to retrieve in ranges. If you've clustered, stored and pre-sorted the data according to the range you wish to extract, this is probably the fastest way on the planet to retrieve the data. Or is this simply more of that good, old MySQLism? You know, the "MySQL can't do it efficiently, so databases sucks"-mantra? but don't do IP geolocation with MySQL if performance is of any importance to you. If you remove the "IP geolocation with" part, I think we have a winner.

by schammy 18y ago

9oliYQjP 18y ago

You bring up a good point, but distributing this as a database is a convenient interchange format. It means I can quickly start playing around with it to see how accurate and precise it is. I downloaded it, imported it into a test database, and was querying it in under 2 minutes. It took me longer than that just to figure out what sort of license I would need to use Maxmind.

trezor 18y ago

But you shouldn't be using a normal database for this type of query anyways - it's SLOW, because you're doing a range search Please explain to me why databases is a bad choice for data which you wish to retrieve in ranges. If you've clustered, stored and pre-sorted the data according to the range you wish to extract, this is probably the fastest way on the planet to retrieve the data. Or is this simply more of that good, old MySQLism? You know, the "MySQL can't do it efficiently, so databases sucks"-mantra? but don't do IP geolocation with MySQL if performance is of any importance to you. If you remove the "IP geolocation with" part, I think we have a winner.