Re: what is better way to write the query

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

 



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


[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