Large inserts and updates

Best practices when doing large inserts and updates in MySQL

[lobo235]

Can multiple INSERT or UPDATE queries be passed to mysql_query() by joining the statements together and separating them by a semi-colon? If so, is this faster than doing each query in a seperate call to mysql_query()?

[mcibor]

Multiple inserts can be done in one query. And I think it's a bit faster.

Best regards
Michal Cibor

[coopster]

You won't be able to join the statements together and separate them with a semicolon like you think. As mentioned, using INSERT statements with multiple VALUES lists to insert several rows at a time is much faster (many times faster in some cases) than using separate single-row INSERT statements. An example of a multiple VALUES list is something like ...

INSERT INTO mytable (fname, lname) VALUES ('John','Doe'), ('Jane','Doe');

These links may be helpful ...

[dev.mysql.com...]
[dev.mysql.com...]

[mcibor]

I thought, that a query is passed exactly to mysql, added a semicolon at the end and parsed by mysql.
But if you say, that it's impossible to connect queries with colon, then I really don't know how the php puts queries into db.

There are so many things to learn about php... I just tried that double query in mysql (and it worked of course :) )

Michal Cibor

[ergophobe]

Really? I thought you needed to be using mysqli_multi_query() [us4.php.net] to be able to do that.

[coopster]

To be honest, I haven't used the improved MySQL extensions yet. I just started reading about them over the weekend. I guess now you actually can start using multiple queries if you so desire.

[lobo235]

The new MySQLi extension is only available in PHP5 correct?

[coopster]

Yes, that is correct.