Re: 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/