How to optimize Jetpack Page Visits Counter?

They are not truly duplicate queries, rather the same kind of query repeated once for every post. It’s not a very efficient query to start with and the problem gets compounded when there are 10 or 20 such queries per page.

One possible solution is to store the data more efficiently so each query takes less time. This would involve abandoning the JetPack functionality and developing your own solution. Such a solution should involve a persistent data cache so relevant data can be fetched even faster.

You could instead implement a persistent data cache for the existing JetPack data as well. When cached data is available, it wouldn’t matter how slow the queries are. The problem with cached data is you’d need to tolerate some inaccurate, stale data. Frequently refreshing the cache defeats its intent.

If you make use of the WP_Object_Cache to store data (non-persistent by default), it can be made persistent by using a persistent caching plugin.

Thank you for your reply, @bcworkz.

Actually, I do not trust my current PHP & WP skills, so I do not prefer risky solutions such as editing the default database structure.

Theoretically, solving my problem should be easy by creating a custom query with a join operation which will return a list of posts with their associated views counter before entering the main posts loop. Inside “The Loop”, all the required fields should be displayed per post without any repeated calls to DB.

However, I do not know how to write such a query. It seems that Jetpack statistics are stored with some kind of encrypted names and very long JSON values in _DDV_Options table. If there was a direct “post_id” field in this table, it would be much easier to use as a foreign key.

Any further support would be appreciated.

OK then, it looks like JetPack already caches data. Didn’t know that (but not all that surprising). Shows how much I know about JetPack. Apparently it doesn’t cache what you’re looking for though. Are you sure the data you’re after is even among the cached data? I think you’re better off going to the original source for the data you need. It’s often saved as part of post meta, whose queries are known to be quite slow.

It’s infeasible to directly access transient data like that due to the hash values appended to the key. You’re forced to work within the intended transient system. Unless the data you’re after is already wrapped up in a single handy transient value, I think you’re better off getting the data from the original source.

I agree that making a single query that gets the data all at once would be superior to the current implementation. In theory it could even be included in the initial main posts query, but altering the main query to do so could get rather messy. Getting all the data in one query independently of the posts query is less ideal, but still a great improvement over one at a time.

You will need to loop through the posts data twice though, but it should go pretty fast since it’s already in memory. The first pass through is to collect the post IDs so we can get the related visitation data. Then we can get it all in one query for use in the second pass through the posts.

The key to all of this is definitively identifying where the source data is kept in the DB. Try identifying a specific post’s ID whose visitation data you know. Search your postmeta table for data related to that ID. See if the data is in the search results. If you cannot identify the source, try asking JetPack support for guidance.

Thank you for your informative reply @bcworkz. Unfortunately, I could not find the source data in posts nor postmeta tables. The views counter is part of the long JSON value in options table which is not very easy to manipulate.

Anyway, I think I am in the right direction thanks to your clarifications. I will close this thread as I have opened a new one in the plugin support forum.

Displaying Post Views in Archive Pages | www.remarpro.com

Edit: I managed to write a custom query to get the required data in a good format without the need to parse JSON in PHP.

SELECT
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            option_value,
            '"post":{"ID":',
            -1
        ),
        ',"post_author"',
        1
    ) AS Post_ID,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(option_value, '"views":', -1),
        ',"years":',
        1
    ) AS Views
FROM
    _DDV_options
WHERE
    option_value LIKE '%"post":{"ID":1234%'
    or option_value LIKE '%"post":{"ID":5678%'

...
    or option_value LIKE '%"post":{"ID":9999%';

• This reply was modified 1 year, 9 months ago by aldiaa.
• This reply was modified 1 year, 9 months ago by aldiaa.