SQL for FiveM: Database Optimization for ESX and QBCore

Your FiveM framework stores everything in a database - player characters, inventories, vehicles, properties, bank accounts. When the database becomes a bottleneck, every interaction in the game slows down.

Database Choice

MySQL vs MariaDB

MariaDB is the recommended choice for FiveM:

• Drop-in MySQL replacement
• Better query optimizer for typical FiveM workloads
• Lower memory footprint
• Active development

SQLite

Don't use SQLite for production FiveM servers. It lacks concurrent write support, which means player actions queue behind each other.

MySQL/MariaDB Configuration

my.cnf Optimization

[mysqld]
# InnoDB settings
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Query cache (MariaDB)
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 4M

# Connection handling
max_connections = 100
wait_timeout = 300
interactive_timeout = 300

# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M

Key Settings Explained

innodb_buffer_pool_size: The most impactful setting. Set to 50-70% of available RAM dedicated to the database. This caches table data and indexes in memory - larger means fewer disk reads.

innodb_flush_log_at_trx_commit = 2: Trades a tiny amount of crash durability for significant write performance. Flushes to OS cache instead of disk on every transaction. For a game server, this trade-off is worth it.

max_connections = 100: FiveM typically needs 20-50 connections. Setting this too high wastes memory on idle connections.

Query Optimization

Common Problems

Missing indexes: The most frequent cause of slow queries.

-- If you query characters by identifier often:
ALTER TABLE characters ADD INDEX idx_identifier (identifier);

-- If you query vehicles by owner:
ALTER TABLE owned_vehicles ADD INDEX idx_owner (owner);

Finding Slow Queries

Enable the slow query log:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

This logs every query taking longer than 0.5 seconds. Review weekly and optimize the worst offenders.

Async Database Access

Never use synchronous database calls in FiveM. They block the server thread:

-- BAD: Blocks the entire server
local result = MySQL.Sync.fetchAll("SELECT * FROM characters WHERE identifier = @id", {
    ["@id"] = identifier
})

-- GOOD: Non-blocking
MySQL.Async.fetchAll("SELECT * FROM characters WHERE identifier = @id", {
    ["@id"] = identifier
}, function(result)
    -- Handle result in callback
end)

-- BETTER: Using oxmysql promises
local result = MySQL.query.await("SELECT * FROM characters WHERE identifier = ?", {identifier})

Batch Operations

When saving multiple players:

-- BAD: 64 individual queries
for _, player in ipairs(players) do
    MySQL.Async.execute("UPDATE characters SET money = @money WHERE id = @id", {
        ["@money"] = player.money, ["@id"] = player.id
    })
end

-- GOOD: Single batch query
local query = "INSERT INTO characters (id, money) VALUES "
local values = {}
for _, player in ipairs(players) do
    table.insert(values, string.format("(%d, %d)", player.id, player.money))
end
query = query .. table.concat(values, ",") .. " ON DUPLICATE KEY UPDATE money = VALUES(money)"
MySQL.Async.execute(query)

Table Structure

Normalization

Keep tables focused. Don't store everything about a player in one giant row:

-- Player base data
CREATE TABLE characters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(60) NOT NULL,
    name VARCHAR(100) NOT NULL,
    money INT DEFAULT 0,
    bank INT DEFAULT 0,
    INDEX idx_identifier (identifier)
);

-- Inventory separate
CREATE TABLE character_inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    character_id INT NOT NULL,
    item VARCHAR(50) NOT NULL,
    count INT DEFAULT 1,
    INDEX idx_character (character_id),
    FOREIGN KEY (character_id) REFERENCES characters(id)
);

Monitoring

Check database performance regularly:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';

Buffer pool hit ratio should be above 99%:

Hit ratio = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

If below 99%, increase innodb_buffer_pool_size.

A well-tuned database on Space-Node's FiveM hosting responds to queries in microseconds, keeping player interactions instant even on busy servers.