Re: Slow response on DBI MySQL query

There are many things you can cache to make executing your subroutine faster.

For example, you can cache the statement handle $obtain_country to fetch a single country:

use vars '$obtain_country';
sub Obtain_GeoIP {
    ...
    $obtain_country ||= $obtain_country = $dba->prepare("select Countr
+y from GeoIP where $lip between Start_int and End_int limit 1");
};
[download]

You could also restructure the query and instead of issuing a query for each IP address, insert all IP addresses into a table and then query the countries for them through SQL.

I'm not sure if a table is the best data structure for a range query and personally, I would just use the Geo::IP module.

Comment on Re: Slow response on DBI MySQL query Select or Download Code

Replies are listed 'Best First'.
Re^2: Slow response on DBI MySQL query by Fortificus (Novice) on Mar 24, 2015 at 14:10 UTC
Corion, Thank you for the response! I am unable to do a single query as there is a lot more processing being done on the rest of the script. Also, I looked at Geo::IP, but the site where the database needs to be downloaded seems down. Are you using it? Any pointers into that module?	[reply]
Re^3: Slow response on DBI MySQL query by Corion (Patriarch) on Mar 24, 2015 at 14:11 UTC
Weird - for me, http://dev.maxmind.com/geoip/legacy/geolite/ works.	[reply]
Re^4: Slow response on DBI MySQL query by Fortificus (Novice) on Mar 24, 2015 at 14:19 UTC
It seems to be a DNS problem on my end (cannot resolve dev.maxmind.com)... I will try in my phone as maybe they are black-holing that domain in the DNS server at work... Are you using this module? Any benchmarks you could share? I would like to have an idea before I start to re-invent the partially working wheel I already have rolling...	[reply]
Re^4: Slow response on DBI MySQL query by Fortificus (Novice) on Mar 24, 2015 at 14:13 UTC
I might be having DNS problems at work then... I get page not available... Are you using this module?	[reply]
Re^5: Slow response on DBI MySQL query by Corion (Patriarch) on Mar 24, 2015 at 14:14 UTC
Re^3: Slow response on DBI MySQL query by fishmonger (Chaplain) on Mar 24, 2015 at 14:21 UTC
Have you tried to install the module via cpan? I just now installed the module and that installation process also included the database without me needing to do anything else.	[reply]
Re^4: Slow response on DBI MySQL query by Fortificus (Novice) on Mar 24, 2015 at 14:25 UTC
Fishmonger, I did not try to install it (yet) as I was looking at the project description and I would not download the DB (likely DNS issues at work). Do you use this module? Have you used it before? I would hate to put a lot of hours to rewrite everything and get back to the same point where it is unusable on the scale I need it to be?	[reply]
Re^3: Slow response on DBI MySQL query by chacham (Prior) on Mar 24, 2015 at 14:41 UTC
unable to do a single query as there is a lot more processing being done One does not preclude the other. Retrieving multiple responses in one batch allows the script to work on all of them as well. If the DB needs to do even more work, perhaps the RDBMS should share more of the load, for example, an SP or more intelligent query be written.	[reply]