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
