How SQL Queries Kill Your FiveM Server — and How to Fix It (2026)

How FiveM Scripts Use Databases

Almost every FiveM resource that persists data — player accounts, vehicles, housing, inventory, jobs — communicates with a MySQL (or MariaDB) database. The database layer sits between your Lua scripts and persistent storage, handling thousands of queries per hour on an active server.

When database queries are fast, players never notice them. When they are slow, the entire server hitches — players freeze, vehicles teleport, and inventory interactions fail silently. In 2026, SQL optimization is a core server administration skill.

---

MySQL Libraries Compared

FiveM uses a resource-based MySQL library to bridge Lua scripts and the database. Three libraries have been widely used:

Library	Status	Async Support	Performance	Recommended
oxmysql	Active	Full	Best	Yes
mysql-async	Legacy	Partial	Good	Migration recommended
ghmattimysql	Legacy	Full	Good	Migration recommended

oxmysql is the current standard. It uses the Node.js mysql2 driver, supports prepared statements, connection pooling, and provides both callback and promise-based APIs. If you are still running mysql-async or ghmattimysql, upgrading to oxmysql is one of the easiest performance wins available.

For detailed database setup instructions, see our MySQL setup guide for FiveM.

---

The SQL Performance Killers

N+1 Queries

The most common database anti-pattern in FiveM scripts:

-- BAD: N+1 pattern (1 query + N additional queries)
local players = MySQL.query.await('SELECT * FROM players')
for _, player in ipairs(players) do
  local vehicles = MySQL.query.await('SELECT * FROM player_vehicles WHERE owner = ?', { player.citizenid })
  -- process vehicles
end

If you have 100 players, this executes 101 queries instead of 1 or 2.

Fix: Use a JOIN or IN clause:

-- GOOD: single query with JOIN
local data = MySQL.query.await([[
  SELECT p.*, v.plate, v.vehicle
  FROM players p
  LEFT JOIN player_vehicles v ON v.owner = p.citizenid
]])

Missing Indexes

Without an index, MySQL scans the entire table for every query. On a table with 10,000 rows, this means checking every single row:

-- Slow without an index on 'citizenid'
SELECT * FROM player_vehicles WHERE citizenid = 'ABC123';

Fix: Add an index on frequently-queried columns:

ALTER TABLE player_vehicles ADD INDEX idx_citizenid (citizenid);

SELECT * (Fetching Everything)

SELECT * retrieves every column, including large text fields, BLOBs, and JSON data you might not need:

-- BAD: fetches all columns including large JSON inventory data
SELECT * FROM players WHERE citizenid = ?;

-- GOOD: fetches only what you need
SELECT citizenid, name, job, money FROM players WHERE citizenid = ?;

Synchronous Queries

This is the most dangerous pattern. Synchronous queries block the server tick until the database responds:

-- DANGEROUS: blocks the entire server tick
local result = MySQL.Sync.fetchAll('SELECT * FROM large_table')

Fix: Always use async:

-- SAFE: non-blocking
MySQL.query('SELECT * FROM large_table', {}, function(result)
  -- handle result in callback
end)

-- OR with await (still async, yields the coroutine)
local result = MySQL.query.await('SELECT * FROM large_table')

---

How to Add Indexes That Matter

Not every column needs an index. Focus on columns used in:

• WHERE clauses — WHERE citizenid = ?, WHERE owner = ?
• JOIN conditions — ON v.owner = p.citizenid
• ORDER BY — ORDER BY created_at DESC

Check which columns are queried most often by your resources, then add targeted indexes:

-- Essential indexes for a typical QBCore/ESX server
ALTER TABLE players ADD INDEX idx_citizenid (citizenid);
ALTER TABLE player_vehicles ADD INDEX idx_owner (citizenid);
ALTER TABLE player_houses ADD INDEX idx_owner (citizenid);
ALTER TABLE items ADD INDEX idx_owner (owner);

Warning: Too many indexes slow down INSERT and UPDATE operations. Only index columns that are frequently used in SELECT queries.

---

Async Query Patterns for FiveM

Callback Style

MySQL.query('SELECT money FROM players WHERE citizenid = ?', { citizenid }, function(result)
  if result and result[1] then
    local money = result[1].money
    -- use money here
  end
end)

Promise/Await Style

local result = MySQL.query.await('SELECT money FROM players WHERE citizenid = ?', { citizenid })
if result and result[1] then
  local money = result[1].money
end

Prepared Statements

oxmysql supports prepared statements, which are both faster and safer:

MySQL.prepare('SELECT money FROM players WHERE citizenid = ?', { citizenid }, function(result)
  -- result is pre-parsed
end)

---

Monitoring Slow Queries

Enable oxmysql Debug Mode

In your server.cfg:

set mysql_slow_query_warning 100

This logs any query taking more than 100 ms to the server console. Reduce the threshold to 50 ms for stricter monitoring.

MySQL Slow Query Log

Enable MySQL's built-in slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

EXPLAIN Your Queries

Use EXPLAIN to understand how MySQL executes a query:

EXPLAIN SELECT * FROM player_vehicles WHERE citizenid = 'ABC123';

Look for:

• type: ALL = full table scan (bad)
• type: ref or type: eq_ref = using index (good)
• rows = estimated rows scanned (lower is better)

---

Common Mistakes and How to Avoid Them

Mistake	Impact	Fix
Using MySQL.Sync in server callbacks	Server freezes during queries	Switch to MySQL.query.await
No connection pool tuning	Connection exhaustion under load	Increase pool size in oxmysql config
Storing JSON as TEXT fields	Slow queries on large JSON blobs	Use specific columns or MySQL JSON type
Running migrations on live server	Table locks cause lag spikes	Schedule migrations during off-peak
No database backups	Data loss during crashes	Configure daily backups in txAdmin

---

Checklist: Database Health Check

• ⬜ Running oxmysql (latest version)
• ⬜ All queries use async methods (no MySQL.Sync)
• ⬜ Indexes exist on frequently-queried columns (citizenid, owner, identifier)
• ⬜ No N+1 query patterns in resource code
• ⬜ SELECT statements specify needed columns (not SELECT *)
• ⬜ Slow query warning is enabled (100 ms threshold)
• ⬜ Database backups run daily
• ⬜ Connection pool is sized for your player count

Well-built premium scripts handle database interactions efficiently. Jobs Creator uses optimized async queries with proper indexing, ensuring your job system never contributes to database lag.

→ Get Jobs Creator by Alone Studios