Missing keys on user IDs?

[Denis-de-Bernardy]

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?

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

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

Ryan you're still in for early?

see also #11914

see also #11375

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

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

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.

cross-referencing #11914

cross-referencing #10329

cross-referencing: #11726

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.

[13576]