How to build a real-time web ranking with cache for MU Online

A ranking that freezes the page for three seconds, shows data from ten minutes ago, and still leaves the game lagging is the wrong calling card for any MU Online server. In this tutorial you'll build a web ranking that gives the sense of updating on its own, protects SQL Server with layered caching, and serves the data through a JSON endpoint the front-end consumes incrementally. The architecture works the same on classic Season 6 servers and on modern versions; what changes is the name of some columns, and that varies by version. The focus here is the web engineering: how to turn an expensive query into a light, fluid experience.

The central idea is to separate three responsibilities that are usually stuck together in the same ranking.php: reading the database, temporarily storing the result, and delivering it to the browser. When those layers are decoupled, you can swap the data source, adjust the cache lifetime, and change the visuals without rewriting everything. It's this separation that lets the ranking seem real-time without charging that cost to the database on every click.

Prerequisites

Before writing a single line, make sure the base environment is up. If you don't have the server running yet, start with the how to create a MU Online server guide and come back here afterward.

• A working MU Online server with an accessible SQL Server (2008, 2014, 2017, or 2019) and the MuOnline database with the Character, MEMB_INFO, Guild, and GuildMember/G_UserList tables.
• A web server with PHP 7.4+ (ideally 8.1), with the sqlsrv or pdo_sqlsrv extension installed and enabled.
• A read-only SQL user dedicated to the site, with no write permission on the game tables.
• Write permission on a cache folder outside the webroot, for example ../cache/.
• Knowledge of HTML, CSS, and modern JavaScript (fetch, async/await).
• Optional: Redis installed, if you already know you'll scale to multiple web nodes.

Confirm the PHP extension with a temporary file containing <?php phpinfo(); and look for sqlsrv. If it doesn't appear, install the Microsoft driver matching your PHP version before continuing. Without that driver nothing below works.

The ranking's layered architecture

Think of a ranking request's flow as a stack. At the top is the player's browser. Below it, the PHP endpoint that responds with JSON. Below that, the cache layer. And only at the bottom is SQL Server. The golden rule is: the deeper a request has to go, the more expensive it is. The goal is to make 95% of requests stop at the cache layer and never touch the database.

With a 60-second TTL, even if 500 players refresh the ranking within that minute, the database is queried only once. The other 499 requests are served from cache in microseconds. That's the difference between a ranking that withstands a launch with a spike of traffic and one that takes the server down on day one.

Connection and database access layer

Isolate the connection in a single file that everything else includes. Never scatter credentials through the code.

<?php
// config/db.php
define('DB_HOST', 'localhost');
define('DB_NAME', 'MuOnline');
define('DB_USER', 'site_readonly');   // read-only user
define('DB_PASS', 'strong_password_here');

function getDB(): mixed {
    static $conn = null;
    if ($conn !== null) return $conn;

    $conn = sqlsrv_connect(DB_HOST, [
        'Database'             => DB_NAME,
        'UID'                  => DB_USER,
        'PWD'                  => DB_PASS,
        'CharacterSet'         => 'UTF-8',
        'ConnectionPooling'    => 1,   // reuses connections
        'LoginTimeout'         => 5,
    ]);

    if ($conn === false) {
        http_response_code(503);
        exit(json_encode(['erro' => 'Database unavailable']));
    }
    return $conn;
}

Using static ensures that within the same request the connection is opened only once. ConnectionPooling lets the driver reuse connections across requests, which reduces the handshake cost. The short LoginTimeout prevents a momentary SQL outage from freezing the page for 30 seconds.

The well-written ranking query

The query is the most performance-sensitive point. Column names vary by version: in many Seasons the reset field is Resets, in others it's ResetCount or lives in a separate table like CharacterReset. Adjust to your schema.

SELECT TOP 100
    ROW_NUMBER() OVER (ORDER BY c.Resets DESC, c.cLevel DESC) AS Posicao,
    c.Name        AS nome,
    c.Class       AS classe,
    c.cLevel      AS level,
    c.Resets      AS resets,
    c.ConnectStat AS online,
    g.G_Name      AS guild
FROM Character c
INNER JOIN MEMB_INFO m ON m.memb___id = c.AccountID
LEFT  JOIN GuildMember g ON g.Name = c.Name
WHERE c.CtlCode = 0        -- excludes GM/admin
  AND m.bloc_code = 0      -- excludes banned accounts
ORDER BY c.Resets DESC, c.cLevel DESC;

Two precautions make a difference here. First, the CtlCode and bloc_code filter happens in the database: data for GMs and banned accounts never reaches PHP. Second, so this ORDER BY doesn't do a full scan every time, create a supporting index:

CREATE INDEX IX_Character_Ranking
ON Character (Resets DESC, cLevel DESC)
INCLUDE (Name, Class, ConnectStat, AccountID);

The INCLUDE index turns the query into an index-only scan in most cases, meaning SQL responds without touching the base table. In databases with tens of thousands of characters, this index alone can cut the query time from seconds to a few milliseconds.

Implementing the file cache

The file cache is simple, reliable, and needs no extra service. The function below wraps the whole "try the cache, recalculate if expired" pattern.

<?php
// lib/cache.php
function cacheRemember(string $chave, int $ttl, callable $callback): array {
    $dir = __DIR__ . '/../cache';
    if (!is_dir($dir)) mkdir($dir, 0755, true);

    $arquivo = "$dir/" . preg_replace('/[^a-z0-9_]/i', '_', $chave) . '.json';

    // 1) Cache valid?
    if (is_file($arquivo) && (time() - filemtime($arquivo)) < $ttl) {
        $dados = json_decode(file_get_contents($arquivo), true);
        if (is_array($dados)) return $dados;
    }

    // 2) Recalculate
    $dados = $callback();

    // 3) Write atomically (avoids corrupted cache under concurrency)
    $tmp = $arquivo . '.' . uniqid('', true) . '.tmp';
    file_put_contents($tmp, json_encode($dados));
    rename($tmp, $arquivo);   // rename is atomic on the same filesystem

    return $dados;
}

The atomic rename detail is important. Under concurrency, two processes may try to rewrite the same cache file at the same time. By writing first to a temporary file and then renaming, you guarantee that no reader gets a half-written JSON. It's a classic mistake that produces intermittent bugs that are hard to reproduce.

The JSON endpoint

Now tie it all together in an endpoint the front-end will consume. It validates the requested tab, applies the cache, and returns pure JSON.

<?php
// api/ranking.php
header('Content-Type: application/json; charset=utf-8');
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../lib/cache.php';

$abasValidas = ['resets', 'level', 'guild', 'online'];
$aba = in_array($_GET['aba'] ?? '', $abasValidas, true) ? $_GET['aba'] : 'resets';

$ttl = ['resets' => 60, 'level' => 60, 'guild' => 120, 'online' => 20][$aba];

$resultado = cacheRemember("ranking_$aba", $ttl, function () use ($aba) {
    $conn = getDB();

    $ordem = match ($aba) {
        'level'  => 'c.cLevel DESC',
        'online' => 'c.ConnectStat DESC, c.cLevel DESC',
        default  => 'c.Resets DESC, c.cLevel DESC',
    };

    $sql = "SELECT TOP 100
                ROW_NUMBER() OVER (ORDER BY $ordem) AS pos,
                c.Name AS nome, c.Class AS classe,
                c.cLevel AS level, c.Resets AS resets, c.ConnectStat AS online
            FROM Character c
            INNER JOIN MEMB_INFO m ON m.memb___id = c.AccountID
            WHERE c.CtlCode = 0 AND m.bloc_code = 0
            ORDER BY $ordem";

    $stmt = sqlsrv_query($conn, $sql);
    $linhas = [];
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        $linhas[] = [
            'pos'    => (int)$row['pos'],
            'nome'   => $row['nome'],
            'classe' => nomeClasse((int)$row['classe']),
            'level'  => (int)$row['level'],
            'resets' => (int)$row['resets'],
            'online' => (bool)$row['online'],
        ];
    }
    return ['gerado_em' => date('c'), 'itens' => $linhas];
});

echo json_encode($resultado);

Notice that the $aba variable only takes one of the allowlist values before entering the SQL string. That's what prevents SQL injection through the query string: since the value never comes raw from the user into the ORDER BY, there's no attack vector. Never concatenate $_GET directly into SQL; use an allowlist for column names and bound parameters for values.

The nomeClasse() function translates the numeric class code into text. The exact map varies by version, but it follows the logic of a base code plus evolution:

<?php
function nomeClasse(int $c): string {
    $mapa = [
        0 => 'Dark Wizard', 1 => 'Soul Master', 2 => 'Grand Master',
        16 => 'Dark Knight', 17 => 'Blade Knight', 18 => 'Blade Master',
        32 => 'Fairy Elf', 33 => 'Muse Elf', 34 => 'High Elf',
        48 => 'Magic Gladiator', 64 => 'Dark Lord',
    ];
    return $mapa[$c] ?? 'Unknown';
}

Incremental front-end updates

On the browser side, the trick to seeming real-time is to poll the endpoint and update only what changed, without reloading the page. That way the list reorganizes with a smooth animation instead of flashing.

<table id="rank"><tbody></tbody></table>

<script>
const corpo = document.querySelector('#rank tbody');
let aba = 'resets';

async function atualizar() {
  try {
    const r = await fetch(`/api/ranking.php?aba=${aba}`, { cache: 'no-store' });
    const { itens } = await r.json();
    renderiza(itens);
  } catch (e) {
    console.warn('Failed to update ranking', e);
  }
}

function renderiza(itens) {
  const html = itens.map(i => `
    <tr class="${i.online ? 'online' : ''}">
      <td>${i.pos}</td>
      <td>${i.nome}</td>
      <td>${i.classe}</td>
      <td>${i.level}</td>
      <td>${i.resets}</td>
    </tr>`).join('');
  corpo.innerHTML = html;
}

atualizar();                 // first load
setInterval(atualizar, 30000); // repoll every 30s
</script>

Since the endpoint already responds from cache most of the time, this 30-second polling per player costs the server almost nothing: each request only touches the cache file. The player, on the other hand, sees the ranking change on its own while the tab is open, and that's the perception of real time you wanted to deliver.

For a more refined visual transition, you can compare the old list with the new one and apply CSS classes for "moved up" or "moved down" on rows that changed position, creating that live-scoreboard effect. This is optional polish, but cheap to implement on top of the base we already have.

Cache warming and the stampede problem

There's a subtle trap in cache with a TTL: when the key expires exactly at peak, several simultaneous requests find the cache stale at the same time and all rush to the database at once. This is called a cache stampede and can take SQL down precisely at the busiest moment.

The most robust solution is to warm the cache externally, with a scheduled task that regenerates the ranking at a fixed interval, independent of visits. The site then always reads an already-ready cache.

<?php
// cron/aquecer.php  — run via Windows Task Scheduler every 60s
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../lib/cache.php';

foreach (['resets', 'level', 'guild', 'online'] as $aba) {
    cacheRemember("ranking_$aba", 0, function () use ($aba) {
        // TTL 0 forces regeneration; same logic as the endpoint
        // ... database query ...
        return gerarRanking($aba);
    });
}

With external warming, the endpoint's TTL can be generous because the cron keeps everything fresh. Visits never fire the heavy query; at most they read a cache a few seconds old. This is the architecture that sustains rankings for large servers without scares.

Security of the ranking layer

The ranking is public, but that doesn't mean carelessness. Three principles: the site's SQL user only has SELECT, so even a code flaw can't alter the game; no sensitive field like password, email, or serial leaves in the queries; and every parameter coming from the browser passes through an allowlist or binding. On top of that, place the cache/ folder outside the webroot or protect it with an .htaccess/IIS rule denying direct HTTP access to the cache .json files, so no one can download the serialized dump.

It's also worth rate-limiting requests to the endpoint. A legitimate player asks for the ranking every 30 seconds; an abusive script may ask 100 times per second. A simple per-IP rate limit, counted in a file or in Redis itself, prevents someone from using the endpoint to pressure your server.

Common errors and fixes

Launch checklist

• sqlsrv extension enabled and tested with phpinfo()
• Site's SQL user with SELECT permission only
• IX_Character_Ranking index created and verified in the execution plan
• CtlCode = 0 and bloc_code = 0 filters present in all queries
• File cache working with atomic writes via rename()
• cache/ folder inaccessible over direct HTTP
• JSON endpoint validating the tab by allowlist
• Front-end polling every 30 seconds with cache: no-store
• Warming cron running every 60 seconds
• Per-IP rate limit configured on the endpoint
• No sensitive field (password, email, serial) exposed in the JSON
• Load test simulating a peak with no noticeable lag in the game

With this structure, your ranking delivers the fluid experience of a live scoreboard while protecting SQL Server from the weight of the queries. The key is always the same idea: compute the expensive result rarely, serve the ready result always.

Frequently asked questions