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

Question

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);
    header( 'Location: /Cron/php/display.php');
?>

Answer 1

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.

Answer 2

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.

Answer 3

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);
header('Location: /Cron/php/display.php');