- name
- MariaDB
- description
- Write efficient MariaDB queries with proper indexing, temporal tables, and clustering.
- metadata
- {"clawdbot":{"emoji":"🦭","requires":{"bins":["mariadb"]},"os":["linux","darwin","win32"]}}
Character Set
- Always use
utf8mb4for tables and connections—full Unicode including emoji utf8mb4_unicode_cifor proper linguistic sorting,utf8mb4_binfor byte comparison- Set connection charset:
SET NAMES utf8mb4or in connection string - Collation mismatch in JOINs forces conversion—kills index usage
Indexing
- TEXT/BLOB columns need prefix length:
INDEX (description(100)) - Composite index order matters—
(a, b)servesWHERE a=?but notWHERE b=? - Foreign keys auto-create index on child table—but verify with
SHOW INDEX - Covering indexes: include all SELECT columns to avoid table lookup
Sequences
CREATE SEQUENCE seq_namefor guaranteed unique IDs across tablesNEXT VALUE FOR seq_nameto get next—survives transaction rollback- Better than auto-increment when you need ID before insert
SETVAL(seq_name, n)to reset—useful for migrations
System Versioning (Temporal Tables)
ALTER TABLE t ADD SYSTEM VERSIONINGto track all historical changesFOR SYSTEM_TIME AS OF '2024-01-01 00:00:00'queries past stateFOR SYSTEM_TIME BETWEEN start AND endfor change history- Invisible columns
row_startandrow_endstore validity period
JSON Handling
JSON_VALUE(col, '$.key')extracts scalar, returns NULL if not foundJSON_QUERY(col, '$.obj')extracts object/array with quotes preservedJSON_TABLE()converts JSON array to rows—powerful for unnestingJSON_VALID()before insert if column isn't strictly typed
Galera Cluster
- All nodes writable—but same-row conflicts cause rollback
wsrep_sync_wait = 1before critical reads—ensures node is synced- Keep transactions small—large transactions increase conflict probability
wsrep_cluster_sizeshould be odd number—avoids split-brain
Window Functions
ROW_NUMBER() OVER (PARTITION BY x ORDER BY y)for ranking within groupsLAG(col, 1) OVER (ORDER BY date)for previous row valueSUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)for running total- CTEs with
WITH cte AS (...)for readable complex queries
Thread Pool
- Enable with
thread_handling=pool-of-threads—better than thread-per-connection thread_pool_size= CPU cores for CPU-bound, higher for I/O-bound- Reduces context switching with many concurrent connections
- Monitor with
SHOW STATUS LIKE 'Threadpool%'
Storage Engines
- InnoDB default—ACID transactions, row locking, crash recovery
- Aria for temporary tables—crash-safe replacement for MyISAM
- MEMORY for caches—data lost on restart, but fast
- Check engine:
SHOW TABLE STATUS WHERE Name='table'
Locking
SELECT ... FOR UPDATElocks rows until commitLOCK TABLES t WRITEfor DDL-like exclusive access—blocks all other sessions- Deadlock detection automatic—one transaction rolled back; must retry
innodb_lock_wait_timeoutdefault 50s—lower for interactive apps
Query Optimization
EXPLAIN ANALYZEfor actual execution times (10.1+)optimizer_tracefor deep dive:SET optimizer_trace='enabled=on'FORCE INDEX (idx)when optimizer chooses wrong indexSTRAIGHT_JOINto force join order—last resort
Backup and Recovery
mariadb-dump --single-transactionfor consistent backup without locksmariadb-backupfor hot InnoDB backup—incremental supported- Binary logs for point-in-time recovery:
mysqlbinlog binlog.000001 | mariadb - Test restores regularly—backups that can't restore aren't backups
Common Errors
- "Too many connections"—increase
max_connectionsor use connection pool - "Lock wait timeout exceeded"—find blocking query with
SHOW ENGINE INNODB STATUS - "Row size too large"—TEXT/BLOB stored off-page, but row pointers have limits
- "Duplicate entry for key"—check unique constraints, use
ON DUPLICATE KEY UPDATE