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? — John Bupit
– John Bupit, Commented Mar 26, 2015 at 15:51

John Bupit · Accepted Answer · 2015-03-26 16:02:16Z

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, 2015 at 16:02

answered Mar 26, 2015 at 16:00

John Bupit

10.7k10 gold badges45 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

chris85 Over a year ago

Do you need to list values every time? I thought it was just comma parenthesis after the first.

John Bupit Over a year ago

Oops, my bad. Fixed it.

Skyfactor_interns Over a year ago

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,....),....;

John Bupit Over a year ago

You can skip the schema if you're inserting all the columns in order.

Rick James · Accepted Answer · 2015-03-28 01:15:41Z

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, 2015 at 1:15

Rick James

144k15 gold badges144 silver badges255 bronze badges

2 Comments

Skyfactor_interns Over a year ago

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.

Rick James Over a year ago

Two different servers is a different challenge. See the FEDERATED engine or MariaDB's FederatedX.

Collectives™ on Stack Overflow

PHP MySQL large quantity of queries being passed,

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related