Performance question regarding GROUP BY...

[bleurose]

We have a modestly large MySQL application (about 10,000,000 rows spread over 40+ tables). Most of our queries are very small and fast (single table or two-table joins with good keys and indexes).

However, we have a few queries that would be described as "long-running", and in particular, they use GROUP BY to aggregate some information. It seems that whenever the query has a GROUP BY, MySQL writes a temp table which then, in turn, puts all other queries into a locked state untl the long-running query finishes. We have tried to simplify the big queries, but as long as the GROUP BY is used, the query plan still shows the temp table being written and then in practice, the locking occurs (BTW, this uses MyISAM, but we tried InnoDB as well in a test, and although it didn't "lock" the other queries, instead it put them into "sleep" and the results were the same). This causes most of our short queries to wait for a long time, which is unacceptable.

It is perfectly okay for the long query to run a REALLY long time as long as it doesn't create an obstacle for the other queries. And this query does NO writing to the database tables themselves, it is only the temp table that is being written.

I have used many other databases and this situation (a temp table being written from a purely read-only query causing other read-only short queries to lock) has never occurred (including Postgres, Oracle, DB2, SQL Server, etc.) I can't believe other MySQL users don't use GROUP BY and I assume we must be missing something really dumb.

Please enlighten us if you can. Is there a way to get rid of the temp table which causes this performance problem without completely rewriting our app?

Thanks!