Composite indexes for MU Online databases: how to kill slow queries

Every MU Online server that grows in concurrent players eventually hits the same bottleneck: the database. Slow login, a ranking page that takes seconds to load, a guild that freezes when opening the member list — most of the time the cause isn't weak hardware, it's the absence of proper indexes on the right tables. A well-designed composite index turns a scan of millions of rows into a direct, millisecond-level lookup. This tutorial shows how to identify slow queries in the MSSQL/MySQL database behind your MuServer, how to design composite indexes for the most common query patterns (login, ranking, inventory, guild), and how to validate the real gain with EXPLAIN/execution plans, without falling into the trap of indexing everything and hurting write performance.

Why the MU Online database suffers from slow queries

Most emulators (MuEmu, IGCN, OpenMU, GVault) use a classic relational schema: Character, Item, Guild, AccountCharacter, MEMB_STAT tables, among others, with dozens of columns and, over time, millions of rows — especially Item (every item of every character) and event logs. Without a proper index, a query filtering by AccountID and CharacterName performs a full table scan, reading row by row. At 50,000 accounts this may still go unnoticed; at 500,000 accounts, login grinds to a halt.

Diagnosing: finding the queries that are actually slow

Don't guess. Enable slow query logging before creating any index:

-- SQL Server: enable Query Store on the MuServer database
ALTER DATABASE MuOnline SET QUERY_STORE = ON;
ALTER DATABASE MuOnline SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

; MySQL: my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

After a few hours with players online, review the log or Query Store for queries with the highest average time × frequency — not the highest isolated time. A 50ms query run 10,000 times per minute weighs more than a 2s query run once an hour.

Reading the execution plan (EXPLAIN)

EXPLAIN SELECT * FROM Character WHERE AccountID = 'player01' AND Ctl1Code = 0;

Look for type: ALL (MySQL) or Table Scan/Clustered Index Scan (SQL Server) — this indicates the database is reading the entire table. The goal is to see type: ref/range or Index Seek, a sign that an index is being used selectively.

What a composite index is and when it solves the problem

A composite index physically orders data by more than one column, in the declared order. It speeds up searches that filter by those columns together, and also searches that filter only by the prefix (the first column, or the first N columns). Column order matters: an index on (AccountID, CharacterName) speeds up WHERE AccountID = X and WHERE AccountID = X AND CharacterName = Y, but does not by itself speed up an isolated WHERE CharacterName = Y.

Mapping the most common MU server queries

Step-by-step: creating a composite index

-- SQL Server
CREATE NONCLUSTERED INDEX IX_Character_Account_Ctl1
ON Character (AccountID, Ctl1Code)
INCLUDE (Name, cLevel, Class);

-- MySQL
CREATE INDEX idx_character_account_ctl1
ON Character (AccountID, Ctl1Code);

The INCLUDE clause in SQL Server matters: it adds columns to the index without making them part of the search key, allowing the query to be answered entirely from the index (a covering index), without going back to the base table.

Ordering columns correctly (selectivity first)

Practical rule: put the most selective column first — the one that most reduces the result set. AccountID is highly selective (few rows per account); Ctl1Code (which usually flags active/deleted characters) has only 2-3 possible values, so it should come after, not before. An index on (Ctl1Code, AccountID) would be far less efficient than (AccountID, Ctl1Code).

Indexes for ORDER BY and ranking

Ranking sites (like ViciadosMU's own public page) run ORDER BY Resets DESC, cLevel DESC every time someone loads the page. Without an index, that's a full in-memory sort on every request. A composite index that already matches the sort order avoids the sort step in the execution plan:

CREATE NONCLUSTERED INDEX IX_Character_Ranking
ON Character (Ctl1Code, Resets DESC, cLevel DESC)
INCLUDE (Name, Class);

This is especially critical if the web ranking queries the database directly (no cache) on every page load — consider also adding a 1-5 minute cache on the application side in addition to the index.

Write and maintenance considerations

Every extra index costs additional CPU and I/O on every INSERT, UPDATE, and DELETE against the table. On Item, a table that receives constant writes (drops, trades, refining), creating too many indexes slows down the GameServer's save loop. Practical guidelines:

• Create at most 3-5 indexes per high-write table.
• Review monthly which indexes are never used (sys.dm_db_index_usage_stats on SQL Server) and remove them.
• Run UPDATE STATISTICS (SQL Server) or ANALYZE TABLE (MySQL) periodically — outdated statistics make the optimizer pick bad plans even with the right index present.

Partial/filtered indexes to reduce size

If 90% of Character rows have Ctl1Code = 0 (active) and you only ever query active characters, a filtered index is smaller and faster:

CREATE NONCLUSTERED INDEX IX_Character_Active
ON Character (AccountID, Name)
WHERE Ctl1Code = 0;

This reduces the index size and improves the cache hit ratio, since fewer pages need to stay in memory.

Validating the gain before and after

Always compare the execution plan before and after creating the index, and measure the actual time:

SET STATISTICS TIME ON;
SELECT * FROM Character WHERE AccountID = 'player01' ORDER BY Resets DESC;
SET STATISTICS TIME OFF;

A typical gain from going from a table scan to an index seek on a 2-million-row table is from seconds down to under 50ms. If the gain doesn't show up, check whether the query actually uses the index columns in the WHERE/ORDER BY clause in the right order.

Continuous monitoring

Set up a job (SQL Server Agent or cron + script) that runs weekly and reports: the week's slowest queries, unused indexes, and index fragmentation above 30%. High fragmentation calls for REORGANIZE (10-30%) or REBUILD (above 30%). Without this routine, the database silently degrades as the player base grows.

Common errors and fixes

Database optimization checklist

• Slow query log or Query Store enabled and monitored.
• Top 10 most frequent/slow queries identified via EXPLAIN/execution plan.
• Composite indexes created in the correct selectivity order for each scenario (login, ranking, items, guild).
• Covering indexes (INCLUDE) used where they reduce extra reads to the table.
• Unused indexes identified and removed.
• Statistics updated (UPDATE STATISTICS/ANALYZE) after large data loads.
• Scheduled job monitoring index fragmentation and usage.
• Performance gain validated before/after with SET STATISTICS TIME or a real benchmark.

With the database responding fast even under load, the next step is to review the infrastructure as a whole — from server sizing to network topology — to make sure the bottleneck doesn't simply move from the database to another component. See the complete guide to setting up a MU Online server to review the end-to-end architecture.

Frequently asked questions