That's a lot of data to return, make sure you factor in data load and transfer time. You may try breaking your query into smaller parts and recombining the results in a scripting language. If you are searching on a range (i.e. date range), break the range into smaller parts and run multiple queries. Divide and conquer, it will scale better.
Brent Baisley On Wed, Sep 3, 2008 at 1:05 PM, Jim Leavitt <[EMAIL PROTECTED]> wrote: > Greetings List, > > We have a medium-large size database application which we are trying to > optimize and I have a few questions. > > Server Specs > 1 Dual Core 2.6 Ghz > 2GB Ram > > Database Specs > 51 Tables > Min 100000 rows, Max 1000000 rows > Total size approx 2GB > > My.cnf > [mysqld] > set-variable=local-infile=0 > log-slow-queries=slow-queries.log > datadir=/var/lib/mysql > socket=/var/lib/mysql/mysql.sock > old_passwords=1 > key_buffer = 512M > max_allowed_packet=4M > sort_buffer_size = 512M > read_buffer_size = 512M > read_rnd_buffer_size = 256M > record_buffer = 256M > myisam_sort_buffer_size = 512M > thread_cache = 128 > query_cache_limit = 1M > query_cache_type = 1 > query_cache_size = 32M > join_buffer = 512M > table_cache = 512 > > > We are having trouble with certain queries which are returning anywhere from > 100000 - 300000 rows. Total query time is taking approx 1 - 2 mins > depending on load. Is there anything in our conf file which could improve > our performance? Are there any hardware recommendations that could help us > improve the speed? Would more memory help us? Any comments or > recommendations are greatly appreciated. > > Thanks much. > > > Jim Leavitt > Developer > Treefrog Interactive Inc. (www.treefrog.ca) > "Bringing the Internet to Life" > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]