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)?