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.

Additional Info:

  • 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
  • Did you try skipping the SQL_CALC_FOUND_ROWS part via the no_found_rows parameter, i.e. if you don't need paging? – birgire Feb 21 '14 at 0:42
  • I'm not sure what you mean by 'extra date'. If you mean in addition to the date the post was created then you can use post_modified to order by. If you mean you need an additional field to post_modified (which is in the post table and therefore doesn't require a join) then this isn't going to be a quick. – Steven Jones Feb 21 '14 at 9:09
  • SQL_CALC_FOUND_ROWS doesn't help all that much peformance wise and for the fact that i need it for paginations. And for the fields yeah its an extra date on top of published and modified dates. I was also thinking of probably adding it in a second table all the ID's of those post this could be helpfull since postmeta contains lot more data and unnecessary to current task. – Musk Feb 21 '14 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.