PHP MySQL large quantity of queries being passed,

Question

I have a PHP script that is passing large quantities of queries to a DB very quickly. Does a MySQL DB queue the queries as they come in if it can't process them at the same speed they are being passed, or do they get lost?

My program has written and passed syntactically correct queries to the DB, but the DB is very far behind in terms of information contained in tables and number of tables.

Some example code (I am slightly new to PHP, so my code/coding style may be horrifying):

//If table has one primary key
        $val = $tblColPkArray[0]; 

        $pkInsert = ", PRIMARY KEY (". $val['COLUMN_NAME'] .")"; 
        $pkColName = $val['COLUMN_NAME'];

        $string = ltrim($string, ","); 

        $oneCreateTableQuery = $beginning . $string . $pkInsert . $end; 

        echo $oneCreateTableQuery . "\n"; 

        $newLink->query($oneCreateTableQuery); 

        $pkValuesInOld = "SELECT " . $pkColName . " FROM " . $tables . ";";

        $pkValsResult = $link->query($pkValuesInOld);

        while($pkValues = $pkValsResult->fetch(PDO::FETCH_ASSOC))
        {
            $pkRowValuesQuery = "SELECT * FROM " . $tables . " WHERE " . $pkColName . " = '" . $pkValues[$pkColName] . "';"; 

            echo $pkRowValuesQuery . "\n";

            $valsOfPkInOldTable = $link->query($pkRowValuesQuery); 

            while($pkVals = $valsOfPkInOldTable->fetch(PDO::FETCH_ASSOC))
            {
                //var_dump($ckVals); 

                $insertBeginning = "INSERT INTO " . $tables . "("; 
                $insertValuesSection = ") VALUES (";
                $insertEnd = ");";
                $keys = ""; 
                $rowValues = ""; 

                foreach($pkVals as $key => $value)
                {
                    $keys = $keys . ", " . $key; 
                    $rowValues = $rowValues . ", '" . $value . "'"; 
                }

                $insertStatement = $insertBeginning . ltrim($keys, ",") . $insertValuesSection . ltrim($rowValues, ",") . $insertEnd; 

                echo $insertStatement . "\n"; 

                $newLink->query($insertStatement);

            }//While loop: Inserting values of old table into new table via INSERT INTO statement 

            //unset();

        } //While loop: Selecting all values from old table based on PK values per table, pass result of that query to next while loop

Can you post some code showing what you're trying to do? Can't you use a [transaction](http://stackoverflow.com/a/2708247/1492578)? — John Bupit, Mar 26 '15 at 15:51

John Bupit · Accepted Answer · 2015-03-26T16:02:16.310

1

You can do this in a single query, instead of calling multiple insert statements. For instance, instead of running these 3 queries,

INSERT INTO table VALUES(1, 2);
INSERT INTO table VALUES(3, 4);
INSERT INTO table VALUES(5, 6);
...

You could run this query:

INSERT INTO table VALUES(1, 2), (3, 4), (5, 6), ...;

edited Mar 26 '15 at 16:02

answered Mar 26 '15 at 16:00

John Bupit

10,406
8
39
75

Do you need to list `values` every time? I thought it was just comma parenthesis after the first. – chris85 Mar 26 '15 at 16:01
Oops, my bad. Fixed it. – John Bupit Mar 26 '15 at 16:02
For this way of doing insert do I need to list the table schema? ie. Insert into table (col1, col2, col3, ...) Values (1, 2, ...), (3,4,....),....; – Skyfactor_interns Mar 26 '15 at 16:20
You can skip the schema if you're inserting all the columns in order. – John Bupit Mar 26 '15 at 16:33

score 1 · Answer 2 · answered Mar 28 '15 at 01:15

1

Looks like you could do even combine the INSERT and SELECT:

INSERT INTO table (...)
    SELECT ... FROM ...;

Furthermore, your nested loops look like this might work:

INSERT INTO table (...)
    SELECT ... FROM ...
               JOIN ...;

That would get it down to one call to ->query() and eliminate most of your code.

answered Mar 28 '15 at 01:15

Rick James

135,179
13
127
222

Does that work with databases residing at different addresses? I had tried that with two databases at the same address (ie. both at 127.0.0.1 vs. one at 127.0.0.1 and the other at www.url.com) and that query did work, but I haven't had success using it for databases at two different addresses. – Skyfactor_interns Mar 30 '15 at 12:24
Two different _servers_ is a different challenge. See the FEDERATED engine or MariaDB's FederatedX. – Rick James Mar 30 '15 at 15:48

PHP MySQL large quantity of queries being passed,

2 Answers2