Re: [PHP] what is better way to write the query
Hi, If possible, write your inserts queries in a text file and use LOAD DATA for bulk inserts. -- Keep Smiling Shafiq Rehman (ZCE) http://www.phpgurru.com | http://shafiq.pk Cell: +92 300 423 9385 On 11/2/07, Andrew Ballard [EMAIL PROTECTED] wrote: On Nov 2, 2007 10:41 AM, afan pasalic [EMAIL PROTECTED] wrote: ...is there any suggestion for the process of inserting up to 5K records at the time ... Is it possible to save your data to a text file and then use one of MySQL's built-in import queries? (I know in some situations it isn't an option because the PHP server and the MySQL server are not able to read from a common location.) Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Keep Smiling Shafiq Rehman (ZCE) http://www.phpgurru.com | http://shafiq.pk Cell: +92 300 423 9385
Re: [PHP] what is better way to write the query
let me write the questions again: what is the difference between these two queries? is there any situation when it's better to use first vs. second solution? is there any suggestion for the process of inserting up to 5K records at the time or this number is so small to consider any optimization? sorry for english :-) -afan afan pasalic wrote: hi, it's maybe more question for mysql list, but since php is involved too... :-) I have php script that inserts into mysql table couple hundreds of records. usually, it looks like: ?php // 1st record $query = INSERT INTO table (col_11, col_12, ... col_1n) VALUES ($value_11, $value_12,... $value_1n ); mysql_query($query) or die ($mysql_error()); // 2nd record $query = INSERT INTO table (col_21, col_22, ... col_2n) VALUES ($value_21, $value_22,... $value_2n ); mysql_query($query) or die ($mysql_error()); ... // last record $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); It also works this way: $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES; $query .= ($value_m1, $value_m2,... $value_mn ), ; $query .= ($value_21, $value_22,... $value_2n ), ; ... $query .= ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); is what's the difference between these two queries? is there any situations when is better to use first vs. second? any suggestion for the process of inserting up to 5K records at the time or this number is so small to consider any optimization? thanks for any help. -afan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] what is better way to write the query
afan pasalic wrote: hi, it's maybe more question for mysql list, but since php is involved too... :-) I have php script that inserts into mysql table couple hundreds of records. usually, it looks like: ?php // 1st record $query = INSERT INTO table (col_11, col_12, ... col_1n) VALUES ($value_11, $value_12,... $value_1n ); mysql_query($query) or die ($mysql_error()); // 2nd record $query = INSERT INTO table (col_21, col_22, ... col_2n) VALUES ($value_21, $value_22,... $value_2n ); mysql_query($query) or die ($mysql_error()); ... // last record $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); It also works this way: $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES; $query .= ($value_m1, $value_m2,... $value_mn ), ; $query .= ($value_21, $value_22,... $value_2n ), ; ... $query .= ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); is what's the difference between these two queries? is there any situations when is better to use first vs. second? any suggestion for the process of inserting up to 5K records at the time or this number is so small to consider any optimization? thanks for any help. -afan I would perform multiple inserts @ a time. This way you save yourself some time by not having mysql rebuild the indexes, if any exist, after each insert statement. -- Jim Lucas Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them. Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] what is better way to write the query
Jim Lucas wrote: afan pasalic wrote: hi, it's maybe more question for mysql list, but since php is involved too... :-) I have php script that inserts into mysql table couple hundreds of records. usually, it looks like: ?php // 1st record $query = INSERT INTO table (col_11, col_12, ... col_1n) VALUES ($value_11, $value_12,... $value_1n ); mysql_query($query) or die ($mysql_error()); // 2nd record $query = INSERT INTO table (col_21, col_22, ... col_2n) VALUES ($value_21, $value_22,... $value_2n ); mysql_query($query) or die ($mysql_error()); ... // last record $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); It also works this way: $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES; $query .= ($value_m1, $value_m2,... $value_mn ), ; $query .= ($value_21, $value_22,... $value_2n ), ; ... $query .= ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); is what's the difference between these two queries? is there any situations when is better to use first vs. second? any suggestion for the process of inserting up to 5K records at the time or this number is so small to consider any optimization? thanks for any help. -afan I would perform multiple inserts @ a time. This way you save yourself some time by not having mysql rebuild the indexes, if any exist, after each insert statement. Indeed, but bear in mind that there is a limit on the size of queries MySQL will accept. Look up the MySQL max_packet_size for details. -Stut -- http://stut.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] what is better way to write the query
afan pasalic wrote: Stut wrote: Jim Lucas wrote: afan pasalic wrote: hi, it's maybe more question for mysql list, but since php is involved too... :-) I have php script that inserts into mysql table couple hundreds of records. usually, it looks like: ?php // 1st record $query = INSERT INTO table (col_11, col_12, ... col_1n) VALUES ($value_11, $value_12,... $value_1n ); mysql_query($query) or die ($mysql_error()); // 2nd record $query = INSERT INTO table (col_21, col_22, ... col_2n) VALUES ($value_21, $value_22,... $value_2n ); mysql_query($query) or die ($mysql_error()); ... // last record $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); It also works this way: $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES; $query .= ($value_m1, $value_m2,... $value_mn ), ; $query .= ($value_21, $value_22,... $value_2n ), ; ... $query .= ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); is what's the difference between these two queries? is there any situations when is better to use first vs. second? any suggestion for the process of inserting up to 5K records at the time or this number is so small to consider any optimization? thanks for any help. -afan I would perform multiple inserts @ a time. This way you save yourself some time by not having mysql rebuild the indexes, if any exist, after each insert statement. Indeed, but bear in mind that there is a limit on the size of queries MySQL will accept. Look up the MySQL max_packet_size for details. -Stut I didn't find max_packet_size in my my.cnf, but found max_allowed_packet - that's the same, right? Indeed, my memory ain't what it used to be. under [mysqld] max_allowed_packet = 1M shouldn't be 1M enough for the query? Depends how big it's gonna get, which is for you to judge. -Stut -- http://stut.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] what is better way to write the query
On Nov 2, 2007 10:41 AM, afan pasalic [EMAIL PROTECTED] wrote: ...is there any suggestion for the process of inserting up to 5K records at the time ... Is it possible to save your data to a text file and then use one of MySQL's built-in import queries? (I know in some situations it isn't an option because the PHP server and the MySQL server are not able to read from a common location.) Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] what is better way to write the query
afan pasalic wrote: Stut wrote: afan pasalic wrote: Stut wrote: Jim Lucas wrote: afan pasalic wrote: hi, it's maybe more question for mysql list, but since php is involved too... :-) I have php script that inserts into mysql table couple hundreds of records. usually, it looks like: ?php // 1st record $query = INSERT INTO table (col_11, col_12, ... col_1n) VALUES ($value_11, $value_12,... $value_1n ); mysql_query($query) or die ($mysql_error()); // 2nd record $query = INSERT INTO table (col_21, col_22, ... col_2n) VALUES ($value_21, $value_22,... $value_2n ); mysql_query($query) or die ($mysql_error()); ... // last record $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); It also works this way: $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES; $query .= ($value_m1, $value_m2,... $value_mn ), ; $query .= ($value_21, $value_22,... $value_2n ), ; ... $query .= ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); is what's the difference between these two queries? is there any situations when is better to use first vs. second? any suggestion for the process of inserting up to 5K records at the time or this number is so small to consider any optimization? thanks for any help. -afan I would perform multiple inserts @ a time. This way you save yourself some time by not having mysql rebuild the indexes, if any exist, after each insert statement. Indeed, but bear in mind that there is a limit on the size of queries MySQL will accept. Look up the MySQL max_packet_size for details. -Stut I didn't find max_packet_size in my my.cnf, but found max_allowed_packet - that's the same, right? Indeed, my memory ain't what it used to be. under [mysqld] max_allowed_packet = 1M shouldn't be 1M enough for the query? Depends how big it's gonna get, which is for you to judge. -Stut I'll run some tests and find what would be the best value. But, multiple inserts (solution no. 2) is the answer, right? It's definitely more efficient, yes. -Stut -- http://stut.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] what is better way to write the query
Stut wrote: afan pasalic wrote: Stut wrote: Jim Lucas wrote: afan pasalic wrote: hi, it's maybe more question for mysql list, but since php is involved too... :-) I have php script that inserts into mysql table couple hundreds of records. usually, it looks like: ?php // 1st record $query = INSERT INTO table (col_11, col_12, ... col_1n) VALUES ($value_11, $value_12,... $value_1n ); mysql_query($query) or die ($mysql_error()); // 2nd record $query = INSERT INTO table (col_21, col_22, ... col_2n) VALUES ($value_21, $value_22,... $value_2n ); mysql_query($query) or die ($mysql_error()); ... // last record $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); It also works this way: $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES; $query .= ($value_m1, $value_m2,... $value_mn ), ; $query .= ($value_21, $value_22,... $value_2n ), ; ... $query .= ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); is what's the difference between these two queries? is there any situations when is better to use first vs. second? any suggestion for the process of inserting up to 5K records at the time or this number is so small to consider any optimization? thanks for any help. -afan I would perform multiple inserts @ a time. This way you save yourself some time by not having mysql rebuild the indexes, if any exist, after each insert statement. Indeed, but bear in mind that there is a limit on the size of queries MySQL will accept. Look up the MySQL max_packet_size for details. -Stut I didn't find max_packet_size in my my.cnf, but found max_allowed_packet - that's the same, right? Indeed, my memory ain't what it used to be. under [mysqld] max_allowed_packet = 1M shouldn't be 1M enough for the query? Depends how big it's gonna get, which is for you to judge. -Stut I'll run some tests and find what would be the best value. But, multiple inserts (solution no. 2) is the answer, right? -afan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] what is better way to write the query
Stut wrote: Jim Lucas wrote: afan pasalic wrote: hi, it's maybe more question for mysql list, but since php is involved too... :-) I have php script that inserts into mysql table couple hundreds of records. usually, it looks like: ?php // 1st record $query = INSERT INTO table (col_11, col_12, ... col_1n) VALUES ($value_11, $value_12,... $value_1n ); mysql_query($query) or die ($mysql_error()); // 2nd record $query = INSERT INTO table (col_21, col_22, ... col_2n) VALUES ($value_21, $value_22,... $value_2n ); mysql_query($query) or die ($mysql_error()); ... // last record $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); It also works this way: $query = INSERT INTO table (col_m1, col_m2, ... col_mn) VALUES; $query .= ($value_m1, $value_m2,... $value_mn ), ; $query .= ($value_21, $value_22,... $value_2n ), ; ... $query .= ($value_m1, $value_m2,... $value_mn ); mysql_query($query) or die ($mysql_error()); is what's the difference between these two queries? is there any situations when is better to use first vs. second? any suggestion for the process of inserting up to 5K records at the time or this number is so small to consider any optimization? thanks for any help. -afan I would perform multiple inserts @ a time. This way you save yourself some time by not having mysql rebuild the indexes, if any exist, after each insert statement. Indeed, but bear in mind that there is a limit on the size of queries MySQL will accept. Look up the MySQL max_packet_size for details. -Stut I didn't find max_packet_size in my my.cnf, but found max_allowed_packet - that's the same, right? under [mysqld] max_allowed_packet = 1M shouldn't be 1M enough for the query? -afan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
