Optimizing MySQL Queries in Pawn: Eliminating Server Freezes

The SA:MP/CRMP server engine operates on a strict single-threaded architecture. This means that absolutely all processes—player movements, timer loops, command processing, anti-cheat checks, and file interactions—are executed sequentially within one main thread. If a single function inside your Pawn script takes 500 milliseconds to execute, the entire server freezes for half a second. For players, this manifests as a sudden ping spike, broken animations, or momentary gameplay stuttering.

The most common culprit behind these micro-freezes and "hangs" is suboptimal interaction with the MySQL database. In this article, we will break down the differences between query types in the BlueG MySQL plugin and demonstrate how a single unoptimized query during authorization can completely paralyze your server.

Blocking Queries (mysql_query) — The Developer's Ultimate Mistake

In older versions of the MySQL plugin (and, unfortunately, in many legacy or custom scripts), the mysql_query() function is used to dispatch SQL statements. This is known as a synchronous (blocking) query.

When the server executes a mysql_query statement, the following events occur:

1. The main server thread completely halts the processing of the active game loop.
2. The server transmits the payload across the network to the hosting provider's MySQL instance.
3. The SA:MP server waits until MySQL scans the database tables, processes the query, and transmits the payload back.
4. Only after intercepting the response does the main server thread awake and resume the game loop.

A Disastrous Example: A player joins the server with the nickname Ivan_Petrov. The script fires a synchronous statement: mysql_query(db_handle, "SELECT * FROM users WHERE name = 'Ivan_Petrov'");. If the user ledger has reached 50,000 records and the name column lacks a database index, the database engine will require roughly 200–400 milliseconds to scan the disk array. Throughout this operation, the server remains completely dead to the other 200 active online players.

Asynchronous Queries (mysql_tquery and mysql_pquery) — The Salvation for Server TPS

Modern revisions of the BlueG MySQL plugin (R39-R41+) introduce a powerful performance-oriented mechanism: asynchrony. The functions mysql_tquery (Threaded Query) and mysql_pquery (Parallel Query) dispatch resource-intensive routines into separate, parallel threads managed by the Linux operating system, completely shielding the main game engine from overhead.

By migrating to an asynchronous architecture, the operational model changes entirely:

1. The main server thread triggers mysql_tquery, immediately offloads the task to a background plugin thread, and instantly continues processing the active game loop (players run seamlessly, damage registers properly, and the TPS stays locked at 50).
2. The background worker thread communicates with the database instance, handles the heavy SQL operations, and awaits the response entirely in the background.
3. Once the dataset is ready, the MySQL plugin signals the main game thread and executes a designated event handler—a callback—where we can safely read the data and grant the player their rank or currency.

Practical Example: Refactoring Authorization to Asynchronous Threads

How NOT to Code (Synchronous Blocking Code):

public OnPlayerConnect(playerid)
{
    new query[128], Cache:result;
    GetPlayerName(playerid, query, MAX_PLAYER_NAME);
    format(query, sizeof(query), "SELECT id FROM users WHERE name = '%s'", query);
    
    result = mysql_query(db_handle, query);
    
    if(cache_num_rows() > 0) {
        ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "Login", "Enter password:", "Submit", "Exit");
    } else {
        ShowPlayerDialog(playerid, DIALOG_REG, DIALOG_STYLE_INPUT, "Register", "Create password:", "Next", "Exit");
    }
    cache_delete(result);
    return 1;
}

The CORRECT Approach (Asynchronous Scripting with Callbacks):

public OnPlayerConnect(playerid)
{
    new query[128], p_name[MAX_PLAYER_NAME];
    GetPlayerName(playerid, p_name, sizeof(p_name));
    
    format(query, sizeof(query), "SELECT id FROM users WHERE name = '%s'", p_name);
    
    mysql_tquery(db_handle, query, "CheckPlayerAccount", "d", playerid);
    return 1;
}

forward CheckPlayerAccount(playerid);
public CheckPlayerAccount(playerid)
{
    if(!IsPlayerConnected(playerid)) return 1;
    
    if(cache_num_rows() > 0) {
        ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "Login", "Enter password:", "Submit", "Exit");
    } else {
        ShowPlayerDialog(playerid, DIALOG_REG, DIALOG_STYLE_INPUT, "Register", "Create password:", "Next", "Exit");
    }
    return 1;
}

What is the Difference Between mysql_tquery and mysql_pquery?

• mysql_tquery: Spawns a single dedicated background thread for processing database statements. All threaded queries are held inside a structured background queue and executed strictly back-to-back. This setup ensures chronological precision—guaranteeing that if you fire a query to save a dataset immediately followed by a statement to load it, they process in the correct chronological order. This is the optimal choice for 95% of server logic routines.
• mysql_pquery: Spawns a multi-threaded pool of parallel processing workers. Statements execute concurrently across multiple physical CPU cores. While this architecture achieves faster lookup speeds, chronological execution order is not guaranteed. Restrict the usage of mysql_pquery strictly to analytical queries or data logging (e.g., logging administrative logs or kill logs) where chronological execution order does not impact structural user account state profiles.

The Golden Rule of Database Indexing

Even an asynchronous query will bottleneck server responsiveness if the structural database configurations are wrong. If a background worker thread gets stuck processing a heavy SQL statement for 5 seconds, the connecting player will remain stuck at the spawn grid indefinitely, waiting for their dialog window to compile.

To avoid this, you must apply database indexes (INDEX / UNIQUE) to all columns utilized within database filter statements (WHERE). In user state structures, indexes should be strictly appended to the name (username) and id columns.

Optimization Command via phpMyAdmin:
Open the SQL editor interface of your database schema on the hosting control dashboard and execute the following query:
ALTER TABLE `users` ADD INDEX (`name`);
This command accelerates account lookups upon player connection by up to several hundred times, minimizing CPU load on your hosting hardware infrastructure.