Execute multiple insert, update, queries separated by ";" at once!

Question

I use remote server to store values into MySQL database.

I don't use any SELECT queries, only INSERT, DELETE and UPDATE.

When I execute 70,000 queries I have trouble with speed.

I was trying mysql_unbuffered_query and I saw little performance profit but it's still too slow.

I remember that some functions in PHP allow me to execute several lines, separated by ;

$something=<<<something

query1 ;
query2 ;
query3 ;

something;

execute($something)

Can someone can help me remember ?

Maybe in PDO ?

Answer 1

The feature you are thinking of is the PHP heredoc syntax. It allows you to do multiple line strings using <<<.

But PDO does not support multiple SQL statements at once. You need to split them up and execute each one individually. You said this was too slow for you, so you may not be able to use PDO.

If you have many statements that are the same but with different parameters, you can use PDO prepared statements. With these, you can prepare the statement once, then just execute it with different parameters. If you have multiples types of statements, you will need to prepare each individually. You can read more about prepared statements here. They also have the benefit of not being vulnerable to SQL injection.

Answer 2

You need mysqli.multi-query. Just create the SQL-command (for example, like you were doing with that heredoc syntax) and execute it. Make sure the separator is configured correctly.

Answer 3

If you don't want to wait, INSERT DELAYED and cousins, with still seperate queries, are the way to go. If you cannot use them (for instance, you are using the InnoDB storage engine), you'll have to feed them to another process which can / is allowed to wait them out.

For the latter option I especially like the job/worker/client concept of gearman, although it takes a little bit of work to get a pool of non-webserver related workers running it's a godsend when you just want to inform a user you will do the requested alterations, possibly with a callback when it's finished, but essentially letting the user go on his/her merry way.

Answer 4

If you posted the code from some of your slower queries, maybe we can help tune them. Often times you miss something just because you've been looking at the code for so long.

Answer 5

Why don't you just call all the statements at once, separated by semi-colons?

mysql_query("
    INSERT INTO something (some, thing) VALUES (123, 456);
    UPDATE AT something STATEMENTS;
    THIRD QUERY; 
    FOURTH MYSQL QUERY; 
    AND SO ON;
");