I have been struggling for quite a while now with this, to put in simple term here is what i do.
- I do an OrderBy with wp_postmeta information
- This allow me to give an additional date to play with.
Reasons for this is i want my users to be able to modify a post without changing the current Order which shows on the main and category pages. This extra date is also used to determined if a post was an "Updated Article" meaning it contains additional information. The whole process works flawlessly so far but having a database containing over 40K+ posts in the database each having this meta_key makes it heavy on the queries. Caching is done also with Transient Cache and secret sauce from the Hosting Provider. But the caching process doesn't seem to help all that much.
So i'm open to critics and suggestion to make the whole process better.
- Avg Query time is : 5 sec
- Query is done via object WP_Query.
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = '_post_updated_date' AND mt1.meta_key = '_post_updated_date' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 10