469,291 Members | 1,797 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,291 developers. It's quick & easy.

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!
Feb 2 '08 #1
0 1211

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Ashish | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.