PHP script optimization for updating database mysql with filename data realtime with files coming through FTP camera

Question

I have a PHP script to update data in database by getting details from file name

<?php
$executionStartTime = microtime(true);
    define('DIR_SEARCH', 'FTPSERVERDEV/images/');
    $files = glob('FTPSERVERDEV/images/*.*', GLOB_BRACE);
    foreach ($files as $filename) {
        $files[$filename] = filemtime($filename);
    }
    arsort($files);

    $newest = array_slice($files, 0, 30);
    //print_r($newest);
    //$file=key($newest);
    foreach ($newest as $key=>$value) {
        //echo 'key:'.$key.' file name:'.$value.'<br>';
        $file_value=end(explode('/', $key));
           // echo $file_value.'<br>';

         //Targhe MAIN_2FZA4_20170328003752448_VEHICLE_DETECTION
        $licenseDetailsEach = explode("_", $file_value);
        $cameraName = end(explode('/',$licenseDetailsEach[0]));
        $licensePlate = $licenseDetailsEach[1];
        $year=substr($licenseDetailsEach[2], 0,4);
        $month=substr($licenseDetailsEach[2], 4,2);
        $day=substr($licenseDetailsEach[2],  6,2);
        $hour = substr($licenseDetailsEach[2], 8,2);
        $min = substr($licenseDetailsEach[2], 10,2);
        $seconds = substr($licenseDetailsEach[2], 12,2);
        $timeRemain = substr($licenseDetailsEach[2], 14,3);
        //echo'<br> year:'. $year.'mm:'.$month.'dd:'.$day;
        $dateAdded = $year.'-'.$month.'-'.$day;
        $timeAdded = $hour.':'.$min.':'.$seconds.' '.$timeRemain;
        $time=$hour.':'.$min.':'.$seconds;
        $created_at= $dateAdded.' '.$time;
        $extention = $licenseDetailsEach[3].'_'.$licenseDetailsEach[4];
        $fileName = end(explode('/',$licenseDetailsEach[0]))."_".$licenseDetailsEach[1]."_".$licenseDetailsEach[2]."_".$licenseDetailsEach[3]."_".$licenseDetailsEach[4];
        $extra=substr($licenseDetailsEach[2],8);

        //echo '<br> cameraName:'.$cameraName.'<br> licensePlate:'.$licensePlate.'<br>  dateAdded:'.$dateAdded.' <br>created at:'.$created_at.' <br>extra:'.$extra.'<br>  fileName:'.$fileName;

        //insert into database with a prepared statement
        $con=new PDO("mysql:host=localhost;dbname=lpm;","root","password");
        $stmt=$con->prepare('select *from license where(fileName=:fileName)');
        $stmt->execute(['fileName'=>$fileName]);
        $is_exists=$stmt->rowCount();
        if(!$is_exists)
        {
            $stmt = $con->prepare('INSERT INTO license (fileName,cameraName,licensePlate,dateAdded,extra,created_at) VALUES (:fileName, :cameraName, :licensePlate, :dateAdded, :extra, :created_at)');
            $stmt->execute(array(
                ':fileName' => $fileName,
                ':cameraName' => $cameraName,
                ':licensePlate' => $licensePlate,
                ':dateAdded' => $dateAdded,
                ':extra' => $extra,
                ':created_at' => $created_at
            ));
        }
    }

    $executionEndTime = microtime(true);

        //The result will be in seconds and milliseconds.
        $seconds = $executionEndTime - $executionStartTime;

        echo "This script took $seconds to execute.";

?>

Now it updates 10-15 data in 5-6 seconds in database. but FTPSERVER is receiving 4-10 files every second.

How can this be optimized or done ?

Can it be done After All ?

you might want to move `$con=new PDO(...` out of the loop – Lawrence Cherone Jun 13 '17 at 09:50 — Lawrence Cherone, Jun 13 '17 at 09:50

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

As referenced by @LawrenceCherone you should check every line within your foreach loop and remove anything that doesn't change on each iteration. For instance the new PDO will be [relatively] expensive and needless to keep making new objects every cycle. I imagine this alone will cause you a significant improvement.

You have two areas of holdup; the database object creation at:

$con=new PDO("mysql:host=localhost;dbname=lpm;","root","password");

and your check if the row already exists at:

$stmt=$con->prepare('select *from license where(fileName=:fileName)');

As an aside, I am unfamiliar with your syntax with brackets, it seems inefficient, and your lack of space between * from is a syntax error.

So, part one: Only create things once

Fixing the Database Object creation to only create one object on page load rather than one per iteration.

$con=new PDO("mysql:host=localhost;dbname=lpm;","root","password");
foreach ($newest as $key=>$value) {

    ....
}

Sorted.

Part two; Making the SQL queries more efficient

This involves dropping the SELECT/COUNT query and instead using MySQL unique indexes (amongst other possible ways) to do something like the following:

 $stmt = $con->prepare('INSERT IGNORE INTO license 
         (fileName,cameraName,licensePlate,dateAdded,extra,created_at) 
         VALUES (:fileName, :cameraName, :licensePlate, :dateAdded, 
         :extra, :created_at)');

The above will silently ignore the insert if there is an issue such as a repetition of a unique index. Judging from your code I expect you may be using the fileName table column as a unique index, so set it as that:

You can add the unique index with CREATE UNIQUE INDEX fileName ON license.

I qualify this may not be the best way of doing this, if you need non-unique fileName references, but the referenced source question has several possible solutions I recommend you explore.

Part three: Index your SQL!

Leading on from Part 2, you should have intelligent competent indexing of your SQL table so that MySQL can check and return results in the most efficient manor possible. There is a lot of literature on here and across the interwebz about this. It is well worth reading up and exploring.

Example:

$con=new PDO("mysql:host=localhost;dbname=lpm;","root","password");
foreach ($newest as $key=>$value) {
        ...

    $stmt = $con->prepare('INSERT IGNORE INTO license 
    (fileName,cameraName,licensePlate,dateAdded,extra,created_at) 
    VALUES (:fileName, :cameraName, :licensePlate, :dateAdded, 
    :extra, :created_at)');
    
    $stmt->execute(array(
        ':fileName' => $fileName,
        ':cameraName' => $cameraName,
        ':licensePlate' => $licensePlate,
        ':dateAdded' => $dateAdded,
        ':extra' => $extra,
        ':created_at' => $created_at
    ));
}

I am not totally sure but script works fine just that it updates the data it should be faster matching with incoming file transfer speed — Ajit Kumar Thakur, Jun 13 '17 at 13:44
@AjitKumarThakur then by definition it doesn't "work fine" if it's slow matching. — Martin, Jun 13 '17 at 15:37

symcbean · Answer 2 · 2017-06-13T10:21:34.060

1

"Optimizing" this code is not going make a significant impact on the performance, scalability nor indeed the functionality of the code (there is at least one race condition). The issues are all about how the code is invoked and the mechanism for identifying files.

Realtime != Polling

You could increase capacity be sharding the workload - but this is just a temporary workaround. Moving, or better yet, deleting the files would give you a bit more headroom, but its still an ugly solution.

Lawrence does make a good suggestion.

edited Jun 13 '17 at 10:21

answered Jun 13 '17 at 10:14

symcbean

47,736
6
59
94

I think simply moving the `new PDO` as referenced by Lawrence would make the biggest difference. after each iteration there's going to be a new PDO object which is going to treacle everything up.... – Martin Jun 13 '17 at 10:24
In the absence of file pruning, the polling time for the ftp server will increase with order o(n) while the database operations will cost at most O(log(n)). The connection in the loop is silly. – symcbean Jun 13 '17 at 15:44

PHP script optimization for updating database mysql with filename data realtime with files coming through FTP camera

2 Answers2

So, part one: Only create things once

Part two; Making the SQL queries more efficient

Part three: Index your SQL!