WordPress.org

Make WordPress Core

Opened 14 years ago

Closed 13 years ago

#2604 closed defect (bug) (fixed)

SQL Query Optimization

Reported by: ryan Owned by: ryan
Milestone: 2.1 Priority: normal
Severity: normal Version: 2.0.2
Component: Administration Keywords: sql db performance
Focuses: Cc:

Description

Improvements suggested by Domas Mituzas.

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

Attachments (4)

sql_chat.txt (23.8 KB) - added by ryan 14 years ago.
Chat with Domas on #wordpress-dev
remove_distinct.diff (1.7 KB) - added by ryan 14 years ago.
Remove DISTINCT from main post query
remove_distinct_groupby.diff (3.1 KB) - added by ryan 14 years ago.
Remove DISTINCT and GROUP BY
type_status_date_key.diff (4.7 KB) - added by ryan 14 years ago.
Add (post_type, post_status, post_date) key and shorten post_type

Download all attachments as: .zip

Change History (15)

#2 @ryan
14 years ago

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

@ryan
14 years ago

Chat with Domas on #wordpress-dev

@ryan
14 years ago

Remove DISTINCT from main post query

#3 @ryan
14 years ago

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
14 years ago

Remove DISTINCT and GROUP BY

#4 @ryan
14 years ago

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

@ryan
14 years ago

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

#5 @ryan
14 years ago

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

#6 @ryan
14 years ago

[3678]

Let's try these on.

#7 @ryan
14 years ago

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.

#8 @ryan
14 years ago

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().

#9 @donncha
14 years ago

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

#11 @matt
13 years ago

  • Resolution set to fixed
  • Status changed from new to closed

These are all done now, I believe.

Note: See TracTickets for help on using tickets.