Question: Are there any large sites out there that are using WordPress 2.0 and having success?

I'm currently working with two larger sites with tens of thousands of posts and comments and both sites crawl with any decent traffic load. I've isolated the issue down to the MySQL queries - they are very inefficient. It seems that the new version adds the current timestamp into the WHERE clause in order to suppress future posts. I also found it odd that the primary query uses post_date_gmt in the WHERE clause but post_date in the ORDER BY. (Using two different variables guarantees at least a second pass through the data, and they contain the same data - order wise.)

I've added extra indexes to the crucial tables which helped slightly. Mark Jaquith came up with a plugin which helped with the timestamp issue so the MySQL query caching is working again, but only for some queries. The sites still crawl with what appears to be a very minimal amount of traffic. The server logs contain hundreds of queries that are taking 20, 40 and even 60 seconds to execute as they process up to 50,000 rows of data.

The same sites worked flawlessly under Wordpress 1.5. Maybe someone needs to write a backwards converter.