At 03:49 PM 2/7/2008, [EMAIL PROTECTED] wrote:
Thanks very much. The default mysql behavior is a little
unusual. What is the parameter in my.cnf to control the cache
size before forcing a flush. I waited for 2 mininutes second
before the output came out without the -q option. My server
is fast enough to read in more than 2 GB data during this time
so the cache would have been filled long before that. Kind
of puzzling.
Have you tried "Select SQL_NO_CACHE col1,col2 from table ..."?
If you are returning more than 10,000 rows at a time, why not use the LIMIT
Offset,Limit to pull in just a few thousand rows at a time, then re-execute
with a new offset to get the next thousand rows etc.. Why tie up the server
with one huge query? You're also transferring 2gb over the network in one
gulp so that can't be efficient either. Break it into smaller queries
should help.
Mike
>In the last episode (Feb 07), [EMAIL PROTECTED] said:
>> I just stumbled on this hard to explain problem. Below
>>
>> select * from BigTable     (BigTable has 5 million rows)
>>
>> takes forever to even print out the first row. as you could see from
>> the sql, it is supposed to print out some rows right away regardless
>> of server config in my.cnf.  What is interesting is the following
>> sql
>>
>> select * from BigTable limit 1000
>>
>> return the rows immediately as expected.
>
>You want the --quick option. From the mysql manpage:
>
> o --quick, -q
>
> Do not cache each query result, print each row as it is
> received. This may slow down the server if the output is
> suspended. With this option, mysql does not use the history
> file.
>
>--
> Dan Nelson
> [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
