How to optimize the PHP code to upload SQL dump

Question

I'm using the code below to create and migrate tables, and while it works, it's very slow. It takes about 10 mins to complete creating about 250 tables and migrating the data. The total file size of the dump is ~1 Mb. Note that this is on localhost, and I'm afraid it'll take 5 times longer when deployed to a server with an unreliable network.

Could this code be optimized to to run within something more like 30 seconds?

function uploadSQL ( $myDbName ) {

        $host = "localhost";
        $uname = "username";
        $pass = "password";
        $database = $myDbName; 
        $conn = new mysqli($host, $uname, $pass, $database);
        $filename = 'db.sql'; 

        $op_data = '';
        $lines = file($filename);
        foreach ($lines as $line)
        {
            if (substr($line, 0, 2) == '--' || $line == '')
            {
                continue;
            }
            $op_data .= $line;
            if (substr(trim($line), -1, 1) == ';')
            {
                $conn->query($op_data);
                $op_data = '';
            }
        }
        echo "Table Created Inside " . $database . " Database.......";

    }

check [this](https://stackoverflow.com/questions/4027769/running-mysql-sql-files-in-php) — juvian, Aug 31 '18 at 16:17

score 0 · Answer 1 · answered Aug 31 '18 at 16:32

You can use cron job for automatically complete this process without waiting. Sometime this process failed for PHP execution timeout.

For increase the execution timeout in php you need to change some setting in your php.ini:

max_execution_time = 60
; also, higher if you must - sets the maximum time in seconds

score 0 · Answer 2 · answered Sep 03 '18 at 05:56

The problem is - this question should not be asked with PHP, it is with the database. During the import, indexes are rebuilt, foreign keys are checked and so on, and this is where the import actually takes a lot of time depending on your database structure. Additionally to this, hardware might be at fault (i.e if the database is on HDD, the import will take noticeably more time than on the SSD drive). I suggest looking into mysqltuner.pl result first, and start optimizing your database from there. Maybe post a question in SO on how to improve the database (as a separate question of course)

disabling SET FOREIGN_KEY_CHECKS=0 before the import and then enabling it with SET FOREIGN_KEY_CHECKS=1 after the import might help a bit, but it won't do all the optimizations you can do.

If all you simply want to do it SCHEDULE the task so you don't have to wait for the database import to finish, you need to implement a queue of tasks (i.e within database table) and handle thee queue via crontab, just as Muyin suggested.

score 0 · Answer 3 · answered Sep 12 '18 at 20:59

0

If the dump comes from mysqldump, then don't use PHP at all. Simply do

mysql -h host.name -u ... -p... < dump_file.sql

answered Sep 12 '18 at 20:59

Rick James

135,179
13
127
222

How to optimize the PHP code to upload SQL dump

3 Answers3