• I have a wordpress multisite setup with many subsites. Some of these have the Top 10 plugin activated.

    I was under the impression that the plugin uses two database tables for every subsite:

    • wp_blogid_top_ten
    • wp_blogid_top_ten_daily

    But on a multisite setup there are also two global tables: wp_top_ten and wp_top_ten_daily.

    mysql> describe wp_top_ten;
    +------------+--------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +------------+--------+------+-----+---------+-------+
    | postnumber | bigint | NO | PRI | NULL | |
    | cntaccess | bigint | NO | | NULL | |
    | blog_id | bigint | NO | PRI | 1 | |
    +------------+--------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

    mysql> describe wp_top_ten_daily;
    +------------+----------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +------------+----------+------+-----+---------+-------+
    | postnumber | bigint | NO | PRI | NULL | |
    | cntaccess | bigint | NO | | NULL | |
    | dp_date | datetime | NO | PRI | NULL | |
    | blog_id | bigint | NO | PRI | 1 | |
    +------------+----------+------+-----+---------+-------+
    4 rows in set (0.01 sec)

    What exactly is stored in the wp_top_ten_daily table on a wp multisite setup? The table currently has over 9 million rows and is growing fast. Is there anything I can do about that?

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

    (@ajay)

    Hi @malimart

    You’re right on single set of tables across multisite. This ensures efficient queries when loading popular posts across the multisite.

    The daily table holds hourly hits per post per blog and hence why it is much bigger in size. There is a maintenance cron available per site to clear any posts older than the default 180 day period. You can enable this in the Settings page.

    These are used to display the custom period (daily) popular posts. It also powers the dashboard in the admin area.

    Please see this article for more information https://webberzone.com/support/knowledgebase/top-10-maintenance-options/

    Thread Starter malimart

    (@malimart)

    I have one more question about the database tables in a multisite setup.

    I can see that the plugin now uses two global tables: wp_top_ten_daily and wp_top_ten.

    However, on some subsites where the plugin is active, there are also site-specific tables. For example, the blog with ID 5787 has two tables: wp_5787_top_ten and wp_5787_top_ten_daily.

    From what I can tell, these site-specific tables are no longer used. I tested deleting them and it had no effect on the statistics for that blog or on the plugin’s functionality, since all the data appears to be stored in the two global tables.

    My assumption is that an older version of the plugin used the per-blog tables, and the current version has migrated everything to the global tables.

    Can you confirm that it’s safe to remove the wp_XXXX_top_ten and wp_XXXX_top_ten_daily tables for each blog?

    Plugin Author Ajay

    (@ajay)

    If your installation is very old then you’d see the original tables per site. A long time back I moved to the single set of tables and had a migrator in place.

    If you’re noticing no issues with the stats then you can safely delete these. Of course please backup before you do so.

Viewing 3 replies - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.