Re: what is better way to write the query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux