Top 10 has become very slow

  • Resolved hardoman

    (@hardoman)


    2 years, 10 months ago

    Since the recent updates > 3.1.0 up to 3.1.3 the plugin has comes very slow when wrking with Sql queries.
    I have checked with query analyzer and found out that the following query takes up to 7 seconds:

    SELECT wp_posts.*,wp_top_ten_daily.postnumber, SUM(wp_top_ten_daily.cntaccess) as visits FROM wp_posts INNER JOIN wp_top_ten_daily ON wp_top_ten_daily.postnumber=wp_posts.ID WHERE 1=1 AND ( wp_posts.post_date <= '2022-04-20 01:14:16' ) AND wp_posts.post_type IN ('post', 'page') AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'inherit')) AND wp_top_ten_daily.blog_id IN ('1') AND wp_top_ten_daily.dp_date >= '2021-04-21 0:0:0' GROUP BY wp_top_ten_daily.postnumber ORDER BY visits DESC LIMIT 0, 21

    I have a widget that outputs most visited 8 pages thought the last year. The max visit count is 20000 visits for the top page if that’s important (but I expect this value should be integer static in db table and doesn’t make difference what the value is).

    WP: 5.6.8
    PHP 7.4.28

    Can you please make any improvements?

Viewing 1 replies (of 1 total)
  • Plugin Author Ajay

    (@ajay)

    @hardoman

    One thing to try is to enable the Maintenance mode in the settings page if you’ve not done so already. This only retains about 6 months of data in the daily tables so it helps clean up massively.

    Do you also have the cache enabled? That should speed up as it doesn’t have to run this query on each load. You can set the cache time to something works comfortably with the site.

    In terms of visit count, the daily table tracks counts per hour so it would then need to add up the posts which is where you see the SUM function in your query.

Viewing 1 replies (of 1 total)
  • The topic ‘Top 10 has become very slow’ is closed to new replies.