Lack of unique key causing extra summary rows

  • Resolved jruca

    (@jruca)


    5 years, 7 months ago

    Since January 2018, our wppsummary table has grown by 3.9 million rows, whereas the previous year and a half only added 39K. The result for us is incredibly slow queries when joining summary and wp_posts. This appears to have been caused by a dropped unique key that makes the ON DUPLICATE KEY UPDATE never fire and each view gets a new record.

    Looking at the activator script, the UNIQUE KEY ID_date is no longer included after version 4.0, and is actually dropped in the upgrade function. What is the reasoning for this change?

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author Hector Cabrera

    (@hcabrera)

    5 years, 7 months ago

    Hi @jruca,

    What is the reasoning for this change?

    It’s this: Custom Time Ranges!

    Before version 4.0, whenever someone visited a post or a page WPP would increment its views count for the day and be done with it. The problem with this approach is that it was impossible to know which were the most viewed post from the last hour or the last 15 minutes, for example.

    Starting version 4.0, WPP started storing views count individually to be able to introduce the Custom Time Range feature which is why the unique key was dropped as it was no longer necessary.

    WPP includes a few tools to help improve performance, including one that limits the size of the summary table: Wiki | Performance.

    Thread Starter jruca

    (@jruca)

    5 years, 7 months ago

    Thanks for quick response. Your explanation makes complete sense and certainly clarifies why and when the unique key was dropped. Thank you for the information.

    As we are only concerned with the last two weeks rather than hourly or smaller, I was able to merge the existing summary records and add a custom key to the table to allow updates rather than inserts. This will work much better for us and greatly reduces database load (56K vs 4M records and 0.05s vs 7+s for the LEFT JOIN with wp_posts).

    With that being said, do you plan to keep the ON DUPLICATE KEY UPDATE part of the view count update in future releases?

    Thanks again for the response and the great plugin!

    Plugin Author Hector Cabrera

    (@hcabrera)

    5 years, 7 months ago

    You’re welcome!

    (…) do you plan to keep the ON DUPLICATE KEY UPDATE part of the view count update in future releases?

    While it’s not really necessary anymore, I kept it in there just in case. So no, I don’t plan on removing it – at least not for the time being.

    There’s another change I’m currently testing that seems to improve vastly database read times. If you have the time, please consider giving it a try and post back your results.

    Finally, I’m also experimenting with a test version of the plugin that stores views data in-memory (Redis, Memcached, etc) and batch updates views count to the database every 5 minutes. So far the results are very, very promising and I’m considering pushing this as a beta feature on the next release because I need more people testing it.

    Both of the above changes, specially the last one, should improve performance drastically and you may even not need to use that custom key anymore. Hopefully, at least 😛

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Lack of unique key causing extra summary rows’ is closed to new replies.