[wp-trac] [WordPress Trac] #37965: Add post_last_activity column to posts database table

[WordPress Trac]

#37965: Add post_last_activity column to posts database table
-------------------------------+-------------------------------------
 Reporter:  johnjamesjacoby    |      Owner:
     Type:  feature request    |     Status:  new
 Priority:  normal             |  Milestone:  Awaiting Review
Component:  Posts, Post Types  |    Version:
 Severity:  normal             |   Keywords:  2nd-opinion needs-patch
  Focuses:  performance        |
-------------------------------+-------------------------------------
 I have a need to order posts by their most recent "activity."

 Activity is purposely ambiguous, but in the context of WordPress core,
 this would likely mean either:
 * the publish time if no comments
 * the time of the most recent comment
 * For something like bbPress, this would mean the `post_date` of the most
 recent child post

 I'm currently storing this data in `postmeta`, but even with type hinting,
 ordering `meta_value` by `DATETIME` is a slow query, particularly when
 there are millions of rows in both the `posts` and `postmeta` database
 tables.

 (I'd considered repurposing the `post_modified`, but unwinding the way
 core uses this internally, paired with not wanting to confuse other
 developers with the oddity, along with `post_modified` not being an
 indexed column, made this a lot of work for not very much gain.)

 To be efficient, this new column would require additional compound indexes
 akin to `type_status_date`. I suspect it would also need an accompanying
 `post_last_activity_gmt` column, as well as some kind of back-fill upgrade
 routine on existing posts.

 Rather than hacking this into bbPress core in a bespoke way, or creating a
 separate plugin to shoe-horn this idea into all of the core queries, I
 figured I'd drop the feature request here for deeper discussion first, to
 get a read on how viable modifying core like this is to everyone.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/37965>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform