Analysis of MySQL query performance problem

Our application needs to run queries on a MySQL (4.1) server that select hundreds of thousands of rows, then group and count() them. From a certain size upward, these queries tend to run extremely slow, even though the underlying machine doesn't fully use its resources. IOW, while these slow queries are executed, there is still processor time, RAM and HD I/O bandwidth available. I suspect that there is potential for optimization * in the design of the queries themselves (to reduce their resource demand) * in the execution environment (to make them make full use of the machine's resources). I could provide the chosen coder with a shell and db login on the relevant machine and a sample SQL statement that results in a slow query What I need is a an analysis of where the time is spent and what the opportunities for optimization are When bidding for this contract, please point out your specific references in the field of MySQL performance optimization. Please don't bid with just your corporate boilerplate attached. ## Deliverables Analysis of the problem and proposals for improvement (or valid reason why no improvement is possible) in written form. ## Platform MySQL, Linux, ext3, Intel