Ticket #2604 (closed defect: fixed)

Opened 2 years ago

Last modified 2 years ago

SQL Query Optimization

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

Description

Improvements suggested by Domas Mituzas.

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

Attachments

sql_chat.txt (23.8 kB) - added by ryan on 03/28/06 06:28:12.
Chat with Domas on #wordpress-dev
remove_distinct.diff (1.7 kB) - added by ryan on 03/31/06 23:13:00.
Remove DISTINCT from main post query
remove_distinct_groupby.diff (3.1 kB) - added by ryan on 04/01/06 00:00:00.
Remove DISTINCT and GROUP BY
type_status_date_key.diff (4.7 kB) - added by ryan on 04/01/06 00:37:30.
Add (post_type, post_status, post_date) key and shorten post_type

Change History

03/27/06 20:28:26 changed by ryan

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

03/28/06 00:08:40 changed by ryan

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

03/28/06 06:28:12 changed by ryan

  • attachment sql_chat.txt added.

Chat with Domas on #wordpress-dev

03/31/06 23:13:00 changed by ryan

  • attachment remove_distinct.diff added.

Remove DISTINCT from main post query

03/31/06 23:18:28 changed by 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.

04/01/06 00:00:00 changed by ryan

  • attachment remove_distinct_groupby.diff added.

Remove DISTINCT and GROUP BY

04/01/06 00:00:59 changed by ryan

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

04/01/06 00:37:30 changed by ryan

  • attachment type_status_date_key.diff added.

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

04/01/06 00:38:34 changed by 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.

04/02/06 00:33:28 changed by ryan

[3678]

Let's try these on.

04/04/06 04:36:30 changed by 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.

04/17/06 03:00:35 changed by 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().

07/24/06 14:28:52 changed by donncha

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

07/24/06 18:25:18 changed by ryan

[4039]

12/01/06 02:46:19 changed by matt

  • status changed from new to closed.
  • resolution set to fixed.

These are all done now, I believe.