FiveM Database Performance: Indexing oxmysql Queries and Fixing MySQL Lag

When players complain about "lag" on a FiveM server, the instinct is to blame a script's tick or the host's CPU. Often the real culprit is sitting one layer down: the database. Every login, every inventory open, every vehicle pull and money transaction is a query, and when those queries are slow or blocking, the symptom shows up as server hitches — a freeze when someone connects, a stutter when ten people open inventories at once, a hang on a scheduled save. This is a guide to fivem database optimization: how DB lag manifests, and the concrete changes that fix it.

How Database Lag Shows Up as Hitches

The server runs your scripts on a main thread. If a resource fires a query and waits for the result on that thread, the entire server waits with it. A query that takes 200ms doesn't just slow one player — it stalls the whole tick. So database problems rarely look like database problems. They look like:

A freeze the moment a player connects (their character/identifier load is querying).
Stutters at peak population when concurrent queries pile up.
A periodic hitch on a clock — your persistence/save interval firing a wave of writes.
One resource that "lags the server" — usually it's hammering the DB in a loop.

The fix isn't a faster CPU. It's making the queries cheaper and making sure they never block the main thread.

Sync vs Async: Never Block the Main Thread

oxmysql gives you both synchronous and asynchronous query styles, and the distinction is the single most important performance decision you'll make.

Synchronous (.await / the sync variants) halts the calling coroutine until the database replies. Used carelessly — especially inside a loop or a frequent event — it blocks and produces exactly the hitches above.
Asynchronous (callbacks / promises that yield) lets the server keep working while the database does its job, and resumes when the result is ready.

The rule: default to async. Reserve synchronous queries for genuine startup/one-shot logic where you truly need the value before continuing, and never put a sync query inside a frequently-fired event or a loop. A single .await in a hot path is one of the most common causes of unexplained server stutter.

Add Indexes to Your Hot Columns

This is the highest-leverage change most servers never make. An index lets MySQL jump straight to matching rows instead of scanning the entire table. Without one, a lookup by identifier or citizenid reads every row in the table — fine at 500 rows, brutal at 500,000.

The columns you look up by constantly are the ones to index. On QBCore that's citizenid; on ESX it's identifier; plus any column you frequently WHERE on — owner columns in vehicle/property tables, plate columns, character ids in inventory tables.

-- find rows by player instantly instead of scanning the table
ALTER TABLE player_vehicles ADD INDEX idx_citizenid (citizenid);
ALTER TABLE owned_vehicles  ADD INDEX idx_owner (owner);

Watch for two traps: a column you join or filter on with no index at all (full table scan every time), and redundant indexes that just slow writes. Index the columns in your WHERE and JOIN clauses — not every column.

Read the Slow Query Log

Stop guessing which query is the problem and let MySQL tell you. The slow query log records every statement that exceeds a threshold.

# my.cnf / my.ini
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

Set long_query_time low (0.5s, even 0.1s while hunting) and turn on log_queries_not_using_indexes — that flag alone surfaces exactly the missing-index problems above. Run the server under real load, then read the log: the queries that appear repeatedly are your targets. Run EXPLAIN on them to confirm whether they're using an index or scanning.

Avoid `SELECT *` and N+1 in Loops

Two anti-patterns do disproportionate damage.

**SELECT * pulls every column even when you need one. On a wide table — inventory blobs, big character JSON — that's wasted bandwidth and memory on every call. Select only what you use: SELECT money, job FROM players WHERE ....

The N+1 pattern is the killer. Code loops over a list and fires one query per item:

-- BAD: one query per vehicle, N round-trips
for _, id in ipairs(ids) do
    MySQL.query.await('SELECT plate FROM player_vehicles WHERE id = ?', { id })
end

Twenty vehicles, twenty blocking round-trips. Replace it with one query using IN, or a join, and fetch the whole set at once:

-- GOOD: one query, one round-trip
MySQL.query('SELECT id, plate FROM player_vehicles WHERE id IN (?)', { ids })

The same logic applies to writes — batch them. Instead of saving each player in a loop with individual UPDATEs, use oxmysql's batch/transaction insert to push many rows in a single call. This matters most at your persistence interval, when everything saves at once.

Connection Pool, Persistence Intervals and MariaDB Tuning

A few server-level levers round it out:

Connection pool. oxmysql pools connections so concurrent queries don't fight over one. Set the pool to a sane size for your population (mysql_connection_string supports connectionLimit). Too small and queries queue; absurdly large just wastes resources.
Persistence intervals. Many frameworks save state on a timer. Saving too often hammers the DB; too rarely risks data loss on a crash. Tune the interval, and make sure the save itself is batched and async so the tick it lands on doesn't hitch.
MariaDB/MySQL memory. The biggest single tuning win is innodb_buffer_pool_size — it's how much of your data and indexes MySQL keeps in RAM. On a dedicated DB box, sizing it to a large share of available memory means hot tables stay cached instead of hitting disk. Leave it at the tiny default and you're reading from disk far more than you should.

Profiling and Where to Go Next

Don't optimize blind — measure which resource is hammering the database. A resource monitor shows you per-resource query volume and timing, so you can point at the actual offender instead of refactoring random scripts. The monitoring tooling at 0resmon-tebex.io is built for exactly this kind of per-resource DB and performance profiling. For well-built scripts that already follow async/indexed patterns out of the box, browse the catalogue at scripts-tebex.io, and for QBCore-specific resources and framework pieces tuned for citizenid lookups, see qb-tebex.io. Profile first, fix the slow queries the log hands you, index your hot columns, and keep every query off the main thread — that sequence resolves the vast majority of "my server lags" problems.