Edan Idzerda Wrote:
-------------------------------------------------------
> I've also posted this problem to the Hyperic HQ
> forums, as the query being used and schema is from
> their application.
>
http://forums.hyperic.com/jiveforums/thread.jspa?m
> essageID=20253伝
>
> But since the problem seems to be odd behavior
> from MySQL, using an InnoDB storage engine, I
> thought perhaps I could get more specific help
> from y'all.
I searched around on Hyperic's JIRA and found they had identified a performance problem with this query on MySQL:
http://jira.hyperic.com/browse/HHQ-2156. Their solution was to rewrite the query into huge UNION. I upgraded to their latest production version (3.2.5) with the updated queries, and my performance problem went away.
I did try on my test database to delete about 400K records of the 1.1M, and the query time was reduced from 26 seconds to 15 seconds. I suppose it's conceivable that the query performed relatively quickly in the first few weeks of using the tool while the table remained relatively small, and it probably just grew slower gradually over time.
This still seems like a MySQL/InnoDB issue to me, as they apparently don't see this query presenting a problem on Oracle or Postgres. But they chose to fix it by rewriting the query, so my motivation to find a resolution within MySQL is now pretty low. :)
Thanks for reading ;)
- edan