1

I'm trying to build a PHP script that will pull the 10 most read posts (weekly) and will return me an XML file (so I can link it to Mailchimp).

Im also using - Post Views Stats Wordpress plugin by cybernetikz - in order to count the post views.

The plugin has its own table wp_cn_track_post structure

--------------
ID | post_id |
--------------

So for every click of the article the plugin is inserting one record with the post id.

The following MySQL Query works, returns the correct posts but it takes forever to load, sometimes it takes 1 hour due to the thousands of records exist in the database.

SELECT *, COUNT(*) as totalViews FROM wp_posts

LEFT JOIN wp_cn_track_post
ON wp_posts.ID = wp_cn_track_post.post_id

AND wp_posts.post_status = 'publish'

AND wp_posts.post_date > '2017-10-16'

AND wp_posts.post_date < '2017-10-22'

GROUP BY wp_posts.ID

ORDER BY totalViews DESC

LIMIT 10

Any ideas/suggestions how can i speed up this query?

Maybe with indexes (unfortunately no experience)?

  • 3
    I would advise against using a plugin that tracks viewer counts locally, they'll suffer from race conditions once multiple people access pages at the same time. Instead, use an external service such as Google Analytics, and pull the information into post meta at regular intervals – Tom J Nowell Oct 23 '17 at 12:57
  • use this method instead of plugin, i hope this will fix your issue, wpbeginner.com/wp-tutorials/… – Muhammad Abdullah Oct 23 '17 at 14:30

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.