 yockTFTCPremium
join:2000-11-21
Miamisburg, OH
kudos:3
1 edit | MySQL supports bulk inserts by simply passing multiple data sets to the VALUES directive, like so:
insert into field, field2, field3 values
(val1, val2, val3),
(val1, val2, val3),
(val1, val2, val3),
etc...
;
You'll need to pay attention to the value of one of two PHP configuration variables too. The variable 'upload_max_size' governs how much data PHP can send directly to a host, while 'post_max_size' governs how much data PHP will allow you to send via HTTP $_POST. If you're using HTTP to send this you'll want the latter, if you're connecting directly to the MySQL server then you want the fomer.
MySQL also has limits on how much data it can receive. If your inserts are failing and PHP isn't kicking back any errors, try increasing MySQL's 'max_allowed_packet'. You can do this via command line by issuing the command:
mysql --max_allowed_packet=24M
Where "24M" is the size in megabytes of the desired value.
One final note. Though MyISAM doesn't support foreign keys, it does support unique indexes. I'm not entirely sure this will help with MyISAM (it's listed as a performance consideration for InnoDB with bulk inserts) but disabling unique checks should speed you up too if you already know that the data folows your uniqueness rules.
To disable that, login to mysql in the command line and issue the following command:
SET unique_checks=0;
I hope this helps you get started. |
yockTFTCPremium
join:2000-11-21
Miamisburg, OH
kudos:3 | I should note too that the MySQL information in this post is for MySQL 5.1. If you're using a 4.x or 6.0 MySQL server, you may need to check on the variable names. |
