High Volume Dashboard Stats

README.md

High Volume Dashboard Stats

Keeps the global "X links, Y clicks" counts on the YOURLS admin dashboard exact and cheap on large installs.

On a big table, the dashboard query SELECT COUNT(keyword), SUM(clicks) FROM yourls_url scans the whole table and can time out. A plain cache is not enough: every link add or delete would force a fresh scan, which times out again (and a timed-out scan caches nothing, so the dashboard stays broken on every reload).

This plugin avoids the rescan entirely for link add/delete, and bounds the only remaining scan.

Requirements

• YOURLS 1.10.4 or later (all SQL fetches are filterable via shunt_fetch_wrapper).
• Optionally, a covering index on clicks (see the trade-off below). Off by default on purpose: it speeds up an occasional admin scan but taxes every redirect.

Install

1. Copy the high-volume-dashboard-stats folder into user/plugins/.
2. Activate High Volume Dashboard Stats in the admin plugins page.
3. On large tables, add the covering index (see below).

How it works

The two numbers behave differently, so they are handled differently:

• Link count - changes only on add/delete. It is kept as an exact counter, adjusted by +1 / -1 on the insert_link / delete_link actions. Adding or removing a link therefore never scans the table; the headline count updates instantly.
• Click sum - grows on every redirect. It is kept as a snapshot, refreshed by a full scan at most once per HVDS_CACHE_TTL. Link add/delete do not trigger this scan: a new link has 0 clicks (sum unchanged), and a deleted link's clicks self-heal at the next refresh.

The global query is intercepted at the fetch level (shunt_fetch_wrapper), matching the exact COUNT()/SUM() statement with the table name resolved at runtime. On a fresh window the maintained values are returned and the query never runs. When the window has elapsed, one scan refreshes the click sum and reseeds the exact link count (healing any drift, eg after a raw-SQL bulk import). Everything else - redirect lookups, filtered/search stats, perform() calls - is passed through untouched, and the get_db_stats filter still runs so other plugins keep working.

Why add/delete no longer times out

Add and delete only touch a counter in the options table. They never run the COUNT()/SUM() query, so they complete instantly regardless of table size.

Covering index on clicks (optional, weigh the trade-off)

With this plugin the link count is maintained incrementally and never scans the table, so the only remaining scan is the periodic click-sum refresh, at most once per HVDS_CACHE_TTL and only when the dashboard is viewed. A covering index speeds that scan up:

ALTER TABLE yourls_url ADD INDEX idx_clicks (clicks);

In InnoDB this index implicitly includes the primary key (keyword), so both COUNT(keyword) and SUM(clicks) are satisfied from it in a single narrow pass.

But it has a permanent cost on the hot path: every redirect runs UPDATE ... SET clicks = clicks + 1, and with the index in place each increment must also move the row's entry in the idx_clicks B-tree (remove the old value, insert the new one). That is an extra secondary-index write on the busiest query of the install, forever, to speed up a scan that runs at most once per window.

The trade-off is redirect volume vs dashboard views:

• Redirects vastly outnumber dashboard views, so paying every redirect to speed up a rare scan is usually the wrong direction. Default: no index.
• With logging on (the default) each redirect already inserts a log row and maintains its index, so idx_clicks only adds incrementally. With logging off (YOURLS_NOSTATS) the redirect does just the clicks UPDATE, so the index weighs proportionally more.
• Add the index only if the periodic sum scan is genuinely too slow for your table size and your redirect volume is low enough to absorb the write cost. Run the ALTER from phpMyAdmin or the SQL CLI, off-peak (online on MariaDB 10 / MySQL 5.6+, but copies the table under a lock on MySQL 5.5).

If the sum scan is a problem but you don't want the index, you can instead maintain the sum incrementally by hooking update_clicks. That removes the scan entirely, but replaces the index's distributed writes with a single shared counter row updated on every redirect, which serializes under high concurrency. At high redirect rates neither is ideal, and the off-hot-path periodic scan is the least-bad option.

Configuration

The click-sum refresh window defaults to 300 seconds. Override in the plugin or in config.php:

define( 'HVDS_CACHE_TTL', 3600 ); // seconds

plugin.php

<?php
/*
Plugin Name: High Volume Dashboard Stats
Plugin URI: https://yourls.org/
Description: Cache the "X links, Y clicks" counts on large installs. YOURLS 1.10.4+.
Version: 1.0
Author: Ozh
Author URI: https://ozh.org/
*/

// No direct call
if ( ! defined( 'YOURLS_ABSPATH' ) ) {
    die();
}

// Option storing the maintained stats: links (exact), clicks (snapshot), time
define( 'HVDS_CACHE_OPTION', 'hvds_global_stats' );

// Click-sum refresh window, in seconds. Override in config.php if needed
if ( ! defined( 'HVDS_CACHE_TTL' ) ) {
    define( 'HVDS_CACHE_TTL', 3600 );
}

// Filter each SQL query
yourls_add_filter( 'shunt_fetch_wrapper', 'hvds_intercept_global_stats', 10, 3 );

// Intercept adding and deleting links
yourls_add_action( 'insert_link', 'hvds_link_added' );
yourls_add_action( 'delete_link', 'hvds_link_deleted' );


/**
 * Build the exact SQL string of the global (unfiltered) dashboard stats query.
 *
 * @return string  The stats SQL string
 */
function hvds_global_stats_sql() {
    return 'SELECT COUNT(keyword) as count, SUM(clicks) as sum FROM `' . YOURLS_DB_TABLE_URL . '` WHERE 1=1 '; // trailing space!
}


/**
 * Intercept the global dashboard stats query and serve maintained values
 *
 * The link count is kept exact and adjusted on add/delete (hvds_link_added() /
 * hvds_link_deleted()), so adding or removing a link never triggers a scan. The
 * click sum is refreshed by a full scan at most once per HVDS_CACHE_TTL
 * A full scan also reseeds the link count, fixing any drift.
 *
 * @param mixed  $pre      Shunt default, or a value if another plugin already answered
 * @param string $method   PDO fetch method being wrapped (eg 'fetchObject', 'perform')
 * @param mixed  ...$args   Wrapped call arguments: $args[0] is the SQL, $args[1] the binds
 * @return mixed           A stdClass {count, sum} for the global query, $pre otherwise
 */
function hvds_intercept_global_stats( $pre, $method, ...$args ) {
    // Re-entrancy guard: skip our own fetch when refreshing the snapshot
    static $running = false;

    // Respect a short-circuit set by another plugin
    if ( yourls_shunt_default() !== $pre ) {
        return $pre;
    }

    // Only the row-fetching call carries a serializable result; ignore perform()
    // (non-serializable PDOStatement) and any other method.
    if ( 'fetchObject' !== $method || $running ) {
        return $pre;
    }

    // Act only on the exact global stats query
    $sql = $args[0] ?? '';
    if ( $sql !== hvds_global_stats_sql() ) {
        return $pre;
    }

    $stats = yourls_get_option( HVDS_CACHE_OPTION );
    $fresh = is_array( $stats )
             && isset( $stats['time'], $stats['links'], $stats['clicks'] )
             && ( time() - $stats['time'] ) < HVDS_CACHE_TTL;

    if ( ! $fresh ) {
        // Full recompute: one scan refreshes the click sum and reseeds the exact
        // link count. Guarded so our own fetch does not re-enter this filter
        $running = true;
        $row     = yourls_get_db('read-get_dashboard_stats')->fetchObject( $sql, $args[1] ?? [] );
        $running = false;

        if ( is_object( $row ) ) {
            $stats = [
                'links'  => (int) $row->count,
                'clicks' => (int) $row->sum,
                'time'   => time(),
            ];
            yourls_update_option( HVDS_CACHE_OPTION, $stats );
        } elseif ( ! is_array( $stats ) ) {
            // Recompute yielded nothing and we have no previous value: let core run
            return $pre;
        }
        // Otherwise keep serving the previous value rather than break the page
    }

    return (object) [ 'count' => $stats['links'], 'sum' => $stats['clicks'] ];
}


/**
 * Adjust the maintained link count by a known delta, without rescanning
 *
 * @param int $delta  Amount to add to the link count (+1 or -1)
 * @return void
 */
function hvds_adjust_link_count( $delta ) {
    $stats = yourls_get_option( HVDS_CACHE_OPTION );

    // Nothing cached yet: the next dashboard load computes a fresh snapshot
    if ( ! is_array( $stats ) || ! isset( $stats['links'] ) ) {
        return;
    }

    $stats['links'] = max( 0, (int) $stats['links'] + $delta );
    yourls_update_option( HVDS_CACHE_OPTION, $stats );
}


/**
 * Increment the maintained link count when a link is created
 *
 * @return void
 */
function hvds_link_added() {
    hvds_adjust_link_count( 1 );
}


/**
 * Decrement the maintained link count when a link is deleted.
 *
 * @return void
 */
function hvds_link_deleted() {
    hvds_adjust_link_count( -1 );
}