a minor tweak should result from doing count(1) instead of count(*)
>From what I know (not much) the * causes the db to do a secondary lookup for the >names of the columns, even though you're not using it at all. As you're selecting every record in the table, I'm pretty sure indexing won't help you... What I've done in the past, depending on how 'real time' the data needs to be, is either 1- schedule a job that runs the query, and puts results in another table, then app hits that table 2- when a record is put into that table, update a second table with new numbers. Ideally, this would be done in a trigger (coming soon to a MySQL version near you) on insert update count_table set total_count = total_count + 1, je_total = je_total + :new.je_total; on update update count_table set je_total = je_total + :new.je_total - :old.je_total; on delete update count_table set total_count = total_count - 1, je_total = je_total - :old.je_total; hope this helps, Dan Greene > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, September 26, 2003 7:34 AM > To: [EMAIL PROTECTED] > Subject: GROUP BY performance on large tables > > > Hi: Issuing a simple group by like this: > > select C_SF, count(*), sum(je) as sum_je > from kp_data > group by C_SF; > > against a large (1.4G) table holding a 5 mln records with 60 columns > takes about 330 secs on my Win2000 development box, > a 2.0GHz P4 w/ 1G RAM and an IDE MAXTOR drive. > > Reducing the column count helped cut time down to 20 secs, but > that is not exactly what we need for this OLAP web app. > I tried the following optimization hints from the ref manual > with moderate results: > > * adding 'order by null' to avoid the final filesort pass. > * increasing buffer sizes to support in-memory operations > key_buffer=64M > table_cache=64 > sort_buffer=64M > read_buffer_size=16M > > How have you been optimizing your queries and DB setups in > comparable situations? > > TIA > Jan Torres > > > -- > 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]
