FiveM Database Optimization: Faster Queries, Less Lag

FiveM Database Optimization: Schemas and Queries That Don’t Lag Your City

Your server runs clean at 30 players and chokes at 120. Resmon looks fine, the CPU has headroom, and yet everything hitches: garages take four seconds to open, the phone app spins, and OneSync warnings creep up. Nine times out of ten the bottleneck isn’t your Lua or your box. It’s the database, and specifically a handful of queries that were quietly scanning the whole table the entire time. A table scan on 800 rows is invisible. The same scan on 80,000 rows is a stutter every player feels.

FiveM database optimization is the practice of designing your schema, indexes, and queries so read and write times stay flat as your city grows. This guide covers the parts that actually move the needle: reading query plans to catch table scans, building indexes that match how you query, using prepared and parameterized statements in oxmysql, and killing the per-tick query patterns that turn a busy night into a slideshow.

Why the database gets slower as your city grows

MySQL doesn’t get slower because it’s tired. It gets slower because most FiveM queries have no index to lean on, so the engine reads every row in the table to answer a question about one. That’s a full table scan. When owned_vehicles holds 500 rows, scanning it to find one player’s cars is free. When eighteen months of characters have pushed it past 60,000 rows, every garage open reads all 60,000 — and if 40 players hit their garage during a race event, you’re scanning millions of rows a minute for data that a single index would have found instantly.

The trap is that this degradation is gradual and invisible in testing. You develop against a near-empty database where everything is fast, ship it, and the lag arrives months later as your player retention succeeds. Optimization is really about designing for the table you’ll have at row one million, not the one you have on launch day.

Read EXPLAIN before players read your patience

Every slow query investigation starts with one keyword. Put EXPLAIN in front of a query and MySQL tells you how it plans to run it instead of running it. This is the single highest-value habit in database work, and most server owners have never used it.

EXPLAIN SELECT vehicle FROM player_vehicles WHERE citizenid = 'ABC12345';

Two columns in the output decide everything:

type — the access method. ALL means a full table scan and is the villain of this whole article. ref or eq_ref means MySQL is using an index to jump straight to matching rows. const is a primary-key hit and the fastest of all.
rows — the estimated number of rows MySQL will examine. If this number tracks your table size instead of the number of rows you actually want back, you have a scan.

Also watch the key column (which index it chose; NULL means none exists to use) and the Extra column. Using filesort and Using temporary mean MySQL is sorting or building scratch tables in memory, usually because your ORDER BY isn’t backed by an index. Using index is the prize: the query was answered entirely from the index without touching the table at all.

Run EXPLAIN on your ten most frequent queries — character load, vehicle lookup, inventory fetch, phone message list. Any that report type: ALL with a large rows value is a lag spike waiting for a busy night.

Indexes that earn their keep

An index is a sorted lookup structure MySQL maintains alongside your table so it can find rows without reading all of them. The rule is simple: index the columns you filter, join, and sort on. On a QBCore server, that means indexing license on the players table (queried on every connect), citizenid on vehicles and inventory tables, and plate wherever you look vehicles up by their tag.

-- Two access patterns, two indexes
ALTER TABLE player_vehicles
  ADD INDEX idx_owner (citizenid),   -- garage: WHERE citizenid = ?
  ADD INDEX idx_plate (plate);       -- lookups: WHERE plate = ?

Where owners waste effort is with composite indexes they don’t understand. A multi-column index obeys the leftmost prefix rule: an index on (receiver, date) helps queries that filter on receiver, or on receiver and date together — but it does nothing for a query that filters on date alone. Column order has to match how you actually query.

-- Phone inbox: filter by receiver, sort by date, in one index
ALTER TABLE phone_messages ADD INDEX idx_recv_date (receiver, date);

The best case is a covering index, where the index contains every column the query needs so MySQL never reads the table row. A SELECT plate FROM player_vehicles WHERE citizenid = ? against the (citizenid, plate) index is answered entirely from the index — that’s the Using index you want in EXPLAIN. This is also why SELECT * quietly hurts: pulling every column forces MySQL back to the table even when a covering index existed, and it drags columns you never use across the wire on every call. Select the three fields you need, not the twenty the table has.

Indexes aren’t free. Each one is extra work on every INSERT and UPDATE, so don’t index a column you never filter on and don’t index a boolean with two possible values. Index what you look up, measure with EXPLAIN, and stop.

Prepared statements and parameterized queries in oxmysql

Parameterizing queries with ? placeholders does two jobs at once. It closes SQL injection — the reason you never build queries with string concatenation — and it lets the database recognize and reuse the query. oxmysql sanitizes every placeholder for you, so there is no excuse for the string.format pattern still floating around old scripts.

-- Never: a player's identifier ends up executed as SQL
MySQL.query(("SELECT * FROM users WHERE identifier = '%s'"):format(id))

-- Always: the value is bound, never parsed as SQL
MySQL.query('SELECT * FROM users WHERE identifier = ?', { id })

For a query you run constantly with the same shape and different values — character load on every connect, item save on every stash close — MySQL.prepare goes a step further. It prepares the statement once and reuses it, which trims parsing overhead on hot paths. It takes only positional ? placeholders and can accept multiple parameter sets in one call.

-- Reused for every login rather than re-parsed each time
local player = MySQL.prepare.await(
  'SELECT * FROM players WHERE license = ?',
  { license }
)

This is the same engineering discipline that separates well-built QBCore scripts from the free releases that torch your database under load — parameterized everywhere, prepared on the hot paths, and never a raw string in sight.

The per-tick query trap

The fastest query is the one you never send. The most common self-inflicted lag in FiveM isn’t a missing index — it’s querying the database from inside a loop. Fetching a config row every frame in a thread, or running one query per player inside a bigger loop, is the N+1 problem: instead of one query for 200 players, you fire 200 queries. Read that data once, cache it in a Lua table, and query the database only when it actually changes.

Writes have their own version of this. Most frameworks save every online player on a timer, and the naive version saves all of them in the same frame:

-- Thundering herd: 200 UPDATEs land in one tick, every 5 minutes
CreateThread(function()
  while true do
    Wait(5 * 60000)
    for _, player in pairs(QBCore.Functions.GetPlayers()) do
      SavePlayer(player)  -- one query each, all at once
    end
  end
end)

Two hundred simultaneous writes spike your connection pool, invite row-lock contention, and can deadlock concurrent saves against the same tables. Fix it either by staggering — add a small Wait between each save so the load spreads across the interval — or by batching them into a single transaction, which is faster and atomic:

local queries = {}
for _, p in pairs(playersToSave) do
  queries[#queries + 1] = {
    'UPDATE players SET money = ? WHERE citizenid = ?',
    { json.encode(p.money), p.citizenid }
  }
end
MySQL.transaction(queries)

The JSON column problem in QBCore and ESX

Both major frameworks store bundled data as JSON blobs — QBCore keeps money, charinfo, job, and metadata as JSON on the players table; ESX does the same with accounts and inventory. That’s convenient for the framework and fine when you read the whole row by primary key. It becomes a problem the moment you try to query inside the JSON — a leaderboard of the richest players, say, or an admin search for everyone above a bank threshold. MySQL can’t index a value buried in a JSON blob, so any WHERE JSON_EXTRACT(money, '$.bank') > ? is a guaranteed full table scan.

The clean fix is a generated column: a derived, read-only column that mirrors one JSON field, which you can then index. It stays in sync automatically and doesn’t interfere with how the framework writes the blob.

ALTER TABLE players
  ADD COLUMN bank_balance INT
    AS (JSON_EXTRACT(money, '$.bank')) STORED,
  ADD INDEX idx_bank (bank_balance);

Now that leaderboard query hits an index instead of scanning every character. And while you’re in the schema: store currency as an integer number of cents, never a FLOAT — floating-point rounding will slowly leak or invent money — and make sure the whole database is utf8mb4 so player names with emoji or non-Latin characters don’t corrupt on save.

Watch the right numbers

You can’t optimize what you can’t see. oxmysql ships two convars that turn guesswork into a list of exact offenders. Set mysql_slow_query_warning to a millisecond threshold (it defaults to 150ms) and any query slower than that is flagged in orange in your console and the oxmysql debug UI. Set mysql_debug true temporarily and every query prints with its parameters and execution time — noisy, but it finds the resource hammering your database in minutes.

Beyond the wrapper, enable MySQL’s own slow_query_log to capture the persistent offenders over a full session, then run EXPLAIN on whatever it catches. Keep an eye on your connection pool too: oxmysql’s default pool size handles most servers, and cranking connectionLimit in the connection string rarely helps — if you’re exhausting connections, the real problem is long-running or unbatched queries holding them open, not a pool that’s too small.

Run this loop once a month and your database stays boring, which is exactly what you want. If you’d rather skip the audit entirely, the low-resmon FiveM scripts worth buying are already built this way — indexed schemas, prepared statements, staggered saves — and you can browse plenty of clean, database-friendly FiveM scripts that won’t hand you this problem in six months.

Frequently asked questions

Do I still need indexes if I use oxmysql? Yes. oxmysql optimizes the wrapper layer — async handling, connection pooling, prepared statements — but it doesn’t touch your schema. A query with no index is a table scan whether it runs through oxmysql or mysql-async. The wrapper delivers the query efficiently; indexes decide how fast MySQL answers it. They solve different problems, and you need both.

How do I find which query is actually lagging my server? Start with mysql_slow_query_warning and mysql_debug true to see live execution times and pin down the guilty resource. Enable MySQL’s slow query log to catch persistent offenders across a full session. Then run EXPLAIN on each one — a type of ALL with a large rows count is your table scan, and a missing index is almost always the cause.

Should I store money as a JSON column or separate columns? Keep the framework’s JSON blob so nothing breaks, but add an indexed generated column for any value you query or aggregate — bank balance, total wealth, playtime. You get schema compatibility and index performance at once. And always store currency as integer cents, never a float, so rounding errors can’t quietly drain or duplicate money over time.