Good morning Monks, I have created a small subroutine to obtain the country from a GeoIP database (below). If I run the query alone (with explain) the response is that it is using its index (Start_int) and the response is 0.00 sec (second paste). The issue that I am getting is that on large scale (over one million records), the response is so slow that it will fail to finish in one day. I put a timer on the executing of 100 entries of the logs (Cisco ASA logs) and when the subroutine is off, the execution time for 100 entries is around 0.03x seconds but as soon as I execute the subroutine, it goes to 1.x to 3.x seconds, which explains why on larger loads it cannot finish. I have added indexes, tweeked the query (using ranges or "between") with no luck. I also tried MyISAM and InnoDB (to try to cache the full table in memory) with no luck either. Please notice that a hash was added to minimize the number of queries, but it does not seem to make a major difference. RedHat 6.6 with latest patches installed. Any advice would be welcome! 
sub Obtain_GeoIP {
        $GeoIP_resolved++;
        $Scountry="";
        $Dcountry="";
        my ($IP) = @_;
        if($IP=~/^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$/ &&(($1<
+=255  && $2<=255 && $3<=255  &&$4<=255 )))
        {
        my $sip = 0;
        my @sip = split(/\./,$IP);
        if (($sip[0] == "10") || ($sip[0] == "192" && $sip[1] == "168"
+) || (($sip[0] == "172" && $sip[1] >= "16") && ($sip[0] == "172" && $
+sip[1] <= "31")))
        {
                return "Internal Network";
        } else {
                if ( exists $geoip_hash{IP} ) { my $country_hash = $ge
+oip_hash{IP}; return $country_hash; }
                my $lip = ( ($sip[0]*($n * $n * $n))+($sip[1]*($n * $n
+))+($sip[2] * $n) + ($sip[3]) );
                # my $obtain_country = $dba->prepare("select Country f
+rom GeoIP where ('$lip' > Start_int) and ('$lip' < End_int) limit 1")
+;
                my $obtain_country = $dba->prepare("select Country fro
+m GeoIP where $lip between Start_int and End_int limit 1");
                $obtain_country->execute();
                my @country = $obtain_country->fetchrow();
                $obtain_country->finish;
                chomp @country;
                $geoip_hash{$IP} = $country[0];
                return $country[0];
        }
        } else {
                print EFILE "IP address sent to Obtain_GeoIP routine w
+as incorrect: $IP - $counter\n";
        }
}



mysql> explain select Country from GeoIP where ('2345565466' > Start_i
+nt) and ('2345565466' < End_int) limit 1;
+----+-------------+-------+-------+-------------------+-----------+--
+-------+------+-------+-------------+
| id | select_type | table | type  | possible_keys     | key       | k
+ey_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+-------------------+-----------+--
+-------+------+-------+-------------+
|  1 | SIMPLE      | GeoIP | range | Start_int,End_int | Start_int | 5
+       | NULL | 40541 | Using where |
+----+-------------+-------+-------+-------------------+-----------+--
+-------+------+-------+-------------+
1 row in set (0.00 sec)


mysql> desc GeoIP;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| Start_int        | int(10) unsigned | YES  | MUL | NULL    |       |
| End_int          | int(10) unsigned | YES  | MUL | NULL    |       |
| Country_Initials | varchar(3)       | YES  |     | NULL    |       |
| Country          | varchar(30)      | YES  |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

[download]

In reply to Slow response on DBI MySQL query by Fortificus

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.