# Check if a site is indexed by google and stores status in Database

This is the script I need to speed up.

Things that I have already done to speed up are:

1. Using single quotes instead of double quotes wherever possible.
2. Created Database Index
3. Replaced queries containing 'select' * with 'select column_names'

It takes around 1.7 seconds per URL on average. I want to know if this can be made any faster by any optimizations in PHP or SQL.

<?php
$servername = '127.0.0.1';$username = 'root';
$password = '';$dbname = 'cron';
$conn = mysqli_connect($servername, $username,$password, $dbname); if (!$conn)
{
die('Connection failed: ' . mysqli_connect_error());
}
$sql = 'SELECT Name FROM domain';$result = mysqli_query($conn,$sql);
while ($row = mysqli_fetch_row($result))
{
$line = (string)$row[0];
$auth = base64_encode('ABC:XYZ');$aContext = array('http' => array('proxy' => 'tcp://IP_ADDRESS', 'request_fulluri' => true, 'header' => 'Proxy-Authorization: Basic'.' '. $auth,),);$cxContext = stream_context_create($aContext); if (strstr(file_get_contents('http://www.google.com/search?q=site:'.$row[0], False, $cxContext), 'did not match any documents')) {$status = FALSE;
}
else
{
$status = TRUE; } date_default_timezone_set('Asia/Kolkata');$date = date('Y-m-d H:i:s');
$sql = "UPDATE domain SET Index_Status = '$status', Last_Checked = '$date' WHERE Name = '$line' ";
$result1 = mysqli_query($conn, $sql); } mysqli_close($conn);
?>

• By far the slowest thing in your code is getting a page from the google server. You cannot speed that up, it is what it is. Let me ask a pertinent question: Why do you want to speed this code up? Can't you run it in the background? May 4 '17 at 10:12

Some ideas that may assist with your design as is.

Assuming you have a large number of domains, you are running a large number of operations during your while loop. Try to break them out if possible, such as:

1) Pull date_default_timezone_set('Asia/Kolkata'); outside and only define it once. And do you need to have the timestamp down to the second? You could just use the date/hour if you put it outside the loop.

2) Is your method to determine if the site is queued the best method? Pulling the whole site to check text returned is a heavy load.

3) You update your MySQL table with the results after each run. Perhaps instead of having 1 UPDATE for every domain, you maintain a list of sites that did have a cache, and run a massive UPDATE afterwards like:

$sql = "UPDATE domain SET Index_Status = 'TRUE', Last_Checked = '$date' WHERE Name IN ('$to_update') ";$sql2 = "UPDATE domain SET Index_Status = 'FALSE', Last_Checked = '$date' WHERE Name NOT IN ('$to_update') ";


4) I have used Blackfire installed on my server and controlled from the browser to help identify where bottlenecks are. This will help you see if the 1.7 second turn around time is 90% in the file_get_contents and 10% in the UPDATEs or 99%/1% and not worth the time to split out or some other result. It's free for the base plan.

• You have a good answer, it would be better if you didn't ask any questions in it. May 4 '17 at 12:46

First of all, changing between double and single quotes is going to make such a trivial difference, you would probably need to measure to microsecond precision to see any performance impact. Use quote styles that make your code most readable in whatever context you are forming strings.

The main thing you can do to speed things up is to parallelize the requests against Google and then batch your update queries for each batch of requests.

Using curl_multi may be a good approach. I actually have a simple REST library that leverages curl_multi that you are free to use if you find it helpful - https://github.com/mikecbrant/php-rest-client

Obviously, making requests in parallel will consume more memory and CPU on your server, so you might need to play around with number of concurrent requests to get your desired compromise of run time vs. resource utilization.

You also need to make sure you have an index on your Name field in the DB.

Don't use root user in MySQL, and especially don't leave root user with empty password.

• ++ for root user and no password, very unsafe practice. May 4 '17 at 4:12
• @MikeBrant I tried using curl_multi with PROXY. It worked a few times and then google 302 error showed up saying that unusual traffic detected and now it is not working. How can I resolve this issue? May 4 '17 at 9:54
• @Kusjal Kale: Clearly Google doesn't like it when you use parallel requests. That's understandable. There is no solution. May 4 '17 at 10:09
• @KIKOSoftware Can't I use proxy servers to send my parallel requests to avoid this problem? May 4 '17 at 14:53
• @KushalKale What you are asking goes well beyond the scope of code review. This is really more a matter of what Google's terms of service dictate. Sure, you may be able to parallelize requests through a number of different proxies to hide the fact that they are coming from the same source, but the question you must then ask yourself is are you still violating terms of service? And if you think you are, do you care? May 4 '17 at 15:09

I would do the following optimization:

• $cxContext can be initialized outside the loop; • use Last_Checked = NOW() in the UPDATE query, instead of $date = date('Y-m-d H:i:s');
• set timezone in MySQL, not in PHP: mysqli_query($conn, "SET timezone = '+05:30'"); • add header Accept-Encoding: gzip to the $cxContext to enable content compression;
• use strpos() just to check if needle in the haystack;
• as others have noted before me: make sure you have an index on your Name field in the DB.

So, it may look something like this:

$servername = '127.0.0.1';$username = 'root';
$password = '';$dbname = 'cron';
$conn = mysqli_connect($servername, $username,$password, $dbname); if (!$conn) {
die('Connection failed: ' . mysqli_connect_error());
}

// Set timezone 'Asia/Kolkata'
mysqli_query($conn, "SET timezone = '+05:30'");$auth = base64_encode('ABC:XYZ');
$aContext = array('http' => array( 'proxy' => 'tcp://IP_ADDRESS', 'request_fulluri' => true, 'header' => "Proxy-Authorization: Basic {$auth}\r\n"
// Enable gzip compression
. "Accept-Encoding: gzip\r\n",
),
);
$cxContext = stream_context_create($aContext);

$sql = 'SELECT Name FROM domain';$result = mysqli_query($conn,$sql);

while ($row = mysqli_fetch_row($result)) {
$line = (string)$row[0];

// Use strpos() and gzdecode()
if (strpos(gzdecode(file_get_contents('http://www.google.com/search?q=site:' . $line, False,$cxContext)), 'did not match any documents') !== FALSE) {
$status = FALSE; } else {$status = TRUE;
}

// Last_Checked = NOW()
$result1 = mysqli_query($conn, "UPDATE domain SET Index_Status = '$status', Last_Checked = NOW() WHERE Name = '$line' ");
}

mysqli_close(\$conn);

• Did you mean gzdecode()? If yes, make sure you added "Accept-Encoding: gzip\r\n" to headers. And add error_reporting(E_ALL); on top of the file to get all errors. May 5 '17 at 11:53
• Yes. I meant gzdecode. And yes, I have added "Accept-Encoding: gzip\r\n" Still this is what I get: Warning: gzdecode(): data error May 5 '17 at 12:20
• Ok. Then try "Accept-Encoding: deflate\r\n" and accordingly gzinflate() for decompression. Similar issue was solved here. May 5 '17 at 12:53