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?

[ryan]

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

[hakre]

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

[hakre]

Ryan you're still in for early?

[Denis-de-Bernardy]

see also #11914

[Denis-de-Bernardy]

see also #11375

[ryan]

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

[ryan]

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

[Denis-de-Bernardy]

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.

[Denis-de-Bernardy]

cross-referencing #11914

[Denis-de-Bernardy]

cross-referencing #10329

[Denis-de-Bernardy]

cross-referencing: #11726

[nacin]

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.

[scribu]

[13576]