On a game server, the sneakiest performance problem usually comes not from the code but from the database. When thousands of players log in at once, a single unindexed query can lock the whole server. MySQL optimization is one of the most critical parts of keeping a busy Metin2 server like Runa2 alive. Here are the core principles.
Measure first: EXPLAIN and the slow query log
Don't optimize by guessing — measure. Turn on MySQL's slow query log to catch slow queries, then inspect each one with EXPLAIN. EXPLAIN shows how MySQL runs the query and whether it uses an index.
Indexes: the biggest win
An index is like a book's index: it lets MySQL jump straight to the row instead of scanning the whole table. Index the columns you use in WHERE, JOIN and ORDER BY:
-- Yavas sorguyu incele
EXPLAIN SELECT * FROM player WHERE name = 'Aslain';
-- "name" sutununa index ekle
CREATE INDEX idx_player_name ON player (name);A query that shows "ALL" (full scan) in EXPLAIN before an index runs much faster afterwards as "ref" or "range".
Avoid SELECT * + use composite indexes
Fetching columns you don't need wastes memory and network. Select only the columns you need, and use a composite index for multiple conditions:
-- Sadece gerekli sutunlar + bilesik (composite) index
SELECT id, level FROM player
WHERE empire = 1 AND level >= 90
ORDER BY level DESC;
CREATE INDEX idx_empire_level ON player (empire, level);In a composite index, column order matters: put the most selective (most narrowing) condition first.
Schema and data types
- Pick the right data type — needlessly large types (like
BIGINTeverywhere) cost space and speed. - Normalize frequently queried tables, but don't overdo it; sometimes a little repetition (denormalization) is faster than JOINs.
- Periodically archive or partition growing log tables.
Maintenance and resilience
- Take regular backups (cron + mysqldump); on a game server, data loss is irreversible.
- As tables grow, keep statistics current with
ANALYZE TABLE. - Handle traffic spikes with a connection pool and a sensible
max_connections.
Frequently asked questions
Can't I just index every column?
No. Every index slows down writes (INSERT/UPDATE) and takes space. Only index columns that queries actually use.
MyISAM or InnoDB?
On modern servers, InnoDB is recommended: with row-level locking and transaction support it's safer under heavy writes.
How do I tell if slowness is from code or the database?
Start with the slow query log and server metrics (CPU, I/O); most of the time a few unindexed queries are the culprit.
Is your server slowing down under load? I can help with database optimization and query tuning — get in touch.