SQL Query Optimization

[ryan]

Improvements suggested by Domas Mituzas.

​http://dammit.lt/2006/03/25/wordpress-sql-query-review/

[ryan]

​http://p.defau.lt/?tiJutSuV86iBJfUPGq_h0g
​http://p.defau.lt/?_hp_YagOMRNnFgeXdhpbpw
​http://p.defau.lt/?DRnl1EN_6SB7UhBfgzLdrw

[ryan]

Attached IRC log of chat with Domas on #wordpress-dev.

[ryan]

Chat with Domas on #wordpress-dev

[ryan]

Remove DISTINCT from main post query

[ryan]

Removing DISTINCT from the main post query resulted in a 6% performance improvement over current trunk. Benchmarked 500 requests using apache bench. The exact same front page was used for each run. Document length was 22205 bytes. This is with the default theme and the default install options. Imported the same 225 posts for each to prime the test with some realistic blog posts.

[ryan]

Remove DISTINCT and GROUP BY

[ryan]

Removing DISTINCT and GROUP BY gives a 10% performance increase over baseline. Same test environment.

[ryan]

Add (post_type, post_status, post_date) key and shorten post_type

[ryan]

Added composite key (post_type, post_status, post_date) and shortened post_type from 100 to 20. Didn't make a difference to performance.

[ryan]

[3678]

Let's try these on.

[ryan]

Just to note, we've already removed timestamps from queries and only check for a post_status of publish in the main post query. No more checks for static.

[ryan]

these two queries:
                    66 Query     SELECT * FROM wp_categories WHERE cat_ID > 0 HAVING category_count > 0 ORDER BY cat_name asc
                    66 Query     SELECT * FROM wp_categories WHERE cat_ID > 0 HAVING link_count > 0 ORDER BY cat_name ASC
could possibly be rewritten into link_count>0 or category_count>0

Anyone want to tackle this part? Eliminate "HAVING". Look in get_categories().

[donncha]

Replacing HAVING with AND works fine for me and should be less costly!

[ryan]

[4039]

[matt]

These are all done now, I believe.