Benchmark

Benchmarking is based on the time spent when to receive a response from exports.
Real query speeds will be reported in the debug UI and in the server console with mysql_debug enabled.

Speeds will vary greatly based on system hardware, database settings, database version, and the current workload.

Script

This script tests export times when using the Lua 5.4 runtime with lib/MySQL.lua syntax.

local function execQuery(msg, fn, query, parameters)
    local start = os.nanotime()
    local result = fn(query, parameters)
    local finish = os.nanotime()
 
    print()
    print(msg)
    print('Executed ' .. (type(query) == 'string' and 1 or #query) .. ' queries in ' .. (finish - start) / 1e6 .. 'ms')
 
    return result
end
 
CreateThread(function()
    local initTable = {
        'DROP TABLE `test_table`',
        [[CREATE TABLE `test_table` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `username` VARCHAR(50) NOT NULL DEFAULT '0',
        `identifier` VARCHAR(50) NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`)
        )]],
        'TRUNCATE `test_table`',
        'ALTER TABLE `test_table` AUTO_INCREMENT = 1',
    }
 
    execQuery('initialise test_table', MySQL.transaction.await, initTable)
 
    -- You might rename "values" as "parameters" for mysql-async compatibility.
    local queries = {
        { query = 'INSERT INTO `test_table` (identifier) VALUES (?)', values = { 'abcdef1' }},
        { query = 'UPDATE `test_table` SET `username` = ? WHERE `id` = LAST_INSERT_ID()', values = { 'bob1' }},
    }
 
    execQuery('{ query: string, values: string }[] transaction', MySQL.transaction.await, queries)
 
    -- You can pass an array of arrays.
    queries = {
        { 'INSERT INTO `test_table` (identifier) VALUES (?)', { 'abcdef2' } },
        { 'UPDATE `test_table` SET `username` = ? WHERE `id` = LAST_INSERT_ID()', { 'bob2' } },
    }
 
    execQuery('[string, any[]][] transaction', MySQL.transaction.await, queries)
 
    -- You can pass an array of queries.
    queries = {
        'INSERT INTO `test_table` (identifier) VALUES ("abcdef3")',
        'UPDATE `test_table` SET `username` = "bob3" WHERE `id` = LAST_INSERT_ID()',
    }
 
    execQuery('string[] transaction', MySQL.transaction.await, queries)
 
    local insertUsers = {}
 
    for i = 1, 10000 do
        insertUsers[i] = { 'INSERT INTO `test_table` (username, identifier) VALUES (?, ?)',
            { 'Testuser_' .. i, 'abcdef' .. i } }
    end
 
    execQuery('insert ' .. #insertUsers .. ' test users', MySQL.transaction.await, insertUsers)
 
    local selectUserIds = {}
 
    for i = 1, 10000 do
        if i % 4 == 0 then
            selectUserIds[math.tointeger(i / 4)] = { 'abcdef' .. i }
        end
    end
 
    execQuery('select every 4th userid', MySQL.prepare.await, 'SELECT `id` FROM `test_table` WHERE `identifier` = ? LIMIT 1', selectUserIds)
 
    local insertid = execQuery('insert', MySQL.insert.await, 'INSERT INTO `test_table` (identifier) VALUES (?)', { 'abcdef' })
    local update = execQuery('update', MySQL.update.await, 'UPDATE `test_table` SET `username` = ? WHERE `id` = ?', { 'bobby', insertid })
    local scalar = execQuery('scalar', MySQL.scalar.await, 'SELECT `username` FROM `test_table` WHERE `id` = ?', { insertid })
    local single = execQuery('single', MySQL.single.await, 'SELECT * FROM `test_table` WHERE `id` = ?', { insertid })
 
    print(json.encode(execQuery('prepare', MySQL.prepare.await, 'SELECT `username` FROM `test_table` WHERE `id` = ?', { insertid })))
    print(insertid, update, scalar, json.encode(single))
 
    execQuery('query', MySQL.query.await, 'SELECT `username` FROM `test_table` WHERE `id` = ? LIMIT 1', { 419 })
end)

Results

[script:test] initialise test_table
[script:test] Executed 4 queries in 42.7789ms
[script:test]
[script:test] { query: string, values: string }[] transaction
[script:test] Executed 2 queries in 8.0145ms
[script:test]
[script:test] [string, any[]][] transaction
[script:test] Executed 2 queries in 4.0278ms
[script:test]
[script:test] string[] transaction
[script:test] Executed 2 queries in 3.4743ms
[script:test]
[script:test] insert 10000 test users
[script:test] Executed 10000 queries in 2064.211ms
[script:test]
[script:test] select every 4th userid
[script:test] Executed 1 queries in 2803.7134ms
[script:test]
[script:test] insert
[script:test] Executed 1 queries in 2.8743ms
[script:test]
[script:test] update
[script:test] Executed 1 queries in 2.8675ms
[script:test]
[script:test] scalar
[script:test] Executed 1 queries in 1.5387ms
[script:test]
[script:test] single
[script:test] Executed 1 queries in 1.3389ms
[script:test]
[script:test] prepare
[script:test] Executed 1 queries in 1.6577ms
[script:test] "bobby"
[script:test] 10004    1       bobby   {"identifier":"abcdef","username":"bobby","id":10004}
[script:test]
[script:test] query
[script:test] Executed 1 queries in 1.1588ms

OxMySQL Common Issues