WordPress.org

Make WordPress Core

Opened 11 years ago

Closed 10 years ago

#11213 closed enhancement (fixed)

Missing keys on user IDs?

Reported by: Denis-de-Bernardy Owned by: ryan
Milestone: 3.0 Priority: normal
Severity: normal Version: 2.9
Component: Database Keywords: has-patch early commit
Focuses: Cc:

Description

Curious to know why there aren't any keys on comments.user_id and posts.post_author.

Aren't we tossing queries here and there that join comments and/or posts with users?

Attachments (1)

11213.diff (1.1 KB) - added by Denis-de-Bernardy 11 years ago.

Download all attachments as: .zip

Change History (16)

#1 @ryan
11 years ago

  • Keywords early added
  • Milestone changed from 2.9 to 3.0

Sounds good, but I don't want to mess with the schema any more in 2.9. Suggesting early 3.0.

#2 @mattrude
11 years ago

  • Cc m@… added

#3 @hakre
11 years ago

Reviewed, change still missing in head for both keys. Suggest to commit it now so we're able to deal with in depth.

#4 @hakre
11 years ago

  • Keywords dev-feedback added

Ryan you're still in for early?

#7 @voyagerfan5761
11 years ago

  • Cc WordPress@… added

#8 @ryan
11 years ago

First let's identify the queries that these keys help. post_author looks like it EXPLAINs better for the post reassignment queries in wpmu_delete_user(), wp_delete_user(), and remove_user_from_blog, the $user_posts_count query in edit.php, and the author query in export_wp().

#9 @ryan
11 years ago

I'm not seeing where user_id in the comments table would help.

#10 @Denis-de-Bernardy
10 years ago

much the same: I'm not entirely sure if deleting a user re-assigns or unsets that user's ID in comments, but if it does the index helps. it could be argued that such an index isn't useful, though, since it's a very infrequent query.

That is *unless* someone decides to build a forum using a custom WP type ("thread") and comments. In this case, fetching a user's participated threads (or more simply in WP, his participated discussions) makes it indispensable.

Re the one on the posts table, my original thought had a lot more to do with the author-related queries.

Note that, in both cases, it may make sense to index (user_id, status) or even (user_id, status, date) instead.

#11 @Denis-de-Bernardy
10 years ago

cross-referencing #11914

#12 @Denis-de-Bernardy
10 years ago

cross-referencing #10329

#13 @Denis-de-Bernardy
10 years ago

cross-referencing: #11726

#14 @nacin
10 years ago

  • Keywords commit added; dev-feedback removed

I believe we decided in IRC that the post_author index would assist? Something also added in #11914, which I believe as indicated as ready go to for 3.0.

#15 @scribu
10 years ago

  • Resolution set to fixed
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.