I'm researching a plan to optimize WordPress SQL/database indexes for highly-trafficked sites, especially ones that have a lot of comments.
The WP code and SQL has already been worked over and are great, of course. But I'm wondering if at HIGH traffic (300+ comments a day, 100K+ pageviews a day) it might not be worth squeezing even more efficient queries -- even if it means more PHP code. It might not, I know. And if I can't do it in a plugin, I will deem any efforts unworthy.
If anyone has advice of things I should look at as I do this, let me know. I've search through this forum and the codex (might have missed something).
So far, I'm trying out an index on "post_date" and "post_date_gmt" on wp_posts right now. I think that might be worth it.
Also, in the interest of being flexible, classes.php builds queries that are slightly inefficient. Like the front page uses this query to get posts:
SELECT DISTINCT * FROM wp_posts WHERE 1=1 AND post_date_gmt <= '2006-07-07 17:50:59' AND (post_status = "publish") AND post_status != "attachment" GROUP BY wp_posts.ID ORDER BY post_date DESC LIMIT 0, 10
So I've hacked classes.php so it uses this:
SELECT * FROM wp_posts WHERE 1=1 AND post_date_gmt <= '2006-07-07 17:47:59' AND (post_status = "publish") AND post_status != "attachment" GROUP BY post_date_gmt DESC ORDER BY post_date_gmt DESC LIMIT 0, 10
It's a bit faster. I'll see if it's enough to make it worth it. Ultimately I don't want to hack classes.php, of course.