Speeding up MySQL through PHP

Crussell

Hi all,

So, I'm building a table of pre-calculated data through a PHP script on a regualr basis (once an hour..)

The table gets populated with a huge amount of data (thousands of rows) with every build and currently my script is INSERTing rows one at a time.

Browsing the net I see there's a way to do multiple INSERTs through one SQL statement.

My question is (and it's probably a stupid one but here goes..) Is it quicker to do a single massive INSERT over possibly thousands of individual ones - my guess is "yes" but I thought I'd ask 🙂

I also saw someone comment on the use of INSERT DELAYED - can anyone shed any light on whether this is worth using ?

TIA

Chris

Sxooter

The speed difference is there, but it's probably not that big. The fastest way is to use some form of bulk load where you read in a csv formatted file or something like that.

The delayed insert is just that. you hand it to MySQL and MySQL inserts it whenever it can get around to it and returns a hearty "thumbs up" to say it was inserted even if it still isn't actually inserted just yet.

Note that each database engine has it's own methods for speeding this kind of thing up, so the further you get from straight inserts the more likel you'll have to do a bit more work should you ever port your application.

Crussell

Thx for the sxooter,

My problem is I'm calculating a load of data on-the-fly then jamming it into a new table so writing it out to csv's wouldnlt make a lot of sense.

I was hoping there was a way to bung a load of data very quickly into the table 🙂

Sxooter

Actually, sticking it all into a CSV table and letting a daemon that runs every x minutes come along and process the file is exactly the way a lot of large batch processing systems work.

Generally speaking, doing a bunch of fairly large inserts with the delayed setting will probably be the fastest way without doing something like bulk loading.

Note that MySQL has a (or used to) a 1 Meg input buffer, so be careful not to make your inserts really huge.

If you were on postgresql or Oracle (or using innodb on mysql) I would advce you to wrap a large number of inserts into a single fairly large transaction.

csn

mysql's "load data" is definitely the fastest way to go. I've used it to import millions of records. The multiple insert mysqlized SQL is probably somewhat faster (than a bunch of single inserts), but I wouldn't use it for inserting too many rows since they'll all probably have to be loaded into memory first. IIRC, you can also turn off auto-commit or something, do a bunch of inserts, then commit and it has the same effect as a big single mutliple insert (and probably use a lot less memory potentially).