Dersgniw
Disco Crunchin
Premium,MVM
join:2001-08-10
behind you
clubs:
 ·Optimum Online
1 edit | php - insert result set to different table
I have 2 tables, in 2 databases.
I want to select data from table AA (on DB 1), then insert that to a temporary table B (on DB 2). Since they are in different DB's, is there an easy way to do this? I'm talking 10,000-20,000 rows so I'm just looking for some efficiency.
--
I Smell Cures! -- Our Hope |
|
GILXA1226
Premium,MVM
join:2000-12-29
London, OH
clubs: | What are you using for a database engine? |
|
Dersgniw
Disco Crunchin
Premium,MVM
join:2001-08-10
behind you
clubs:
·Optimum Online
| said by GILXA1226  :What are you using for a database engine? MySql
--
I Smell Cures! -- Our Hope |
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH | MyISAM or InnoDB? |
|
Dersgniw
Disco Crunchin
Premium,MVM
join:2001-08-10
behind you
clubs: | MyISAM |
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH
1 edit | MySQL supports bulk inserts by simply passing multiple data sets to the VALUES directive, like so:
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. |
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH | 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. |
|
