Code Addition is Slowing Page Down

Question

My client ordered another addition to the script, but I can't figure out how to fix the slowdown? The table has about 50,000 rows.

 while($stats = mysql_fetch_array($get_stats)) {
  if ($stats['ip'] == gethostbyaddr($stats['ip'])) { // new code
   $is_undef = "Yes";            // causing problems
  } else { $is_undef = "No"; }      // end new code

 echo "<tr><td>" . date("d M Y g:i a ", strtotime($stats['date'])) . "</td><td>" .
       $stats['ip'] . "</td><td>" .
          parse_url_domain($stats['ref_url']) . "</td><td>" .
             $is_undef . "</td></tr>";
 }

This is the query:

 $get_stats = mysql_query("SELECT * FROM visitors WHERE site='$_GET[site]' AND date >= '$start_date' AND date <= '$end_date' ");

Answer 1

I think that you might have an issue with "gethostbyaddr". Looping over that 50k times is going to be REALLY slow.

Also, not that it is relevant to the question, but you might want to think about sql injection a little bit. I hope that isn't the actual query that you are running. If so, someone can simply drop your table.

Answer 2

I guess gethostbyaddr() is slow because it fails to find a host for the IP address. What's the purpose of the following expression anyway?

$stats['ip'] == gethostbyaddr($stats['ip'])

Assume $stats['ip'] is 127.0.0.1 and the corresponding host is localhost. So you'd compare 127.0.0.1 (an IP address) to localhost (a host name).

Answer 3

from the comments at http://php.net/gethostbyaddr:

"Just wanted to let everyone know that gethostbyaddr() takes more than 20 seconds to respond if the IP address is not listed in DNS."

I'd say looking these hostnames up in a loop is probably slowing you down.

also the variables you're sending to that query need to be properly escaped or you're really asking for trouble.

Answer 4

DNS requests can take quite a long time, especially across 50k records. If your client demands having the hostname instead of IP address for records, you might want to run some kind of background process to cache the hostnames instead of looking them up every page load.

also, most ISPs use blocks of ip addresses, so you could start to build tables which track ip ranges and hostmasks for ISPs to cut out the DNS lookups

Answer 5

I think the gethostbyaddr() call is slowing you down.

See http://us3.php.net/manual/en/function.gethostbyaddr.php#88920

Answer 6

I would suggest doing this check before you add each address to your database table (ie once each instead of 50,000 times every time the data is viewed!