David,
I'm responding to this particular post because, after going back (again and agin) to look over these posts, I think this is by far the best suggestion/solution you've given. I'm pretty certain that something between 4.1.14 and 5.0.16 has changed specifically in the InnoDB execution, because I simply cannot figure out why an identical query, run on identical schema, with almost identical data sets, would be *that* much different on the one box than the other.
In short, your suggestion to further limit the query with the timestamp column is certainly a good idea, especially with the table data growing at a decent clip.
If you wouldn't mind, try taking a look through the bug database for InnoDB stuff that is similar to your problem here. If you don't find one, perhaps it is worth putting together a reproducable test case and submitting a bug...
I know that InnoDB tables traditionally don't fare well on queries of this type:
SELECT MAX(some_indexed_col) FROM some_table;
whereas MyISAM tables *do* perform well for the above query. Reason: InnoDB indexes do not store the count/max of an index's records whereas MyISAM does. It might be worth converting a test database to MyISAM tables are running aquick test comparison to see if this InnoDB limitation is the cause of the problems...
Good luck, David!
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com