MariaDB

Write efficient MariaDB queries with proper indexing, temporal tables, and clustering.

Character Set

• Always use utf8mb4 for tables and connections—full Unicode including emoji
• utf8mb4_unicode_ci for proper linguistic sorting, utf8mb4_bin for byte comparison
• Set connection charset: SET NAMES utf8mb4 or 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) serves WHERE a=? but not WHERE 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_name for guaranteed unique IDs across tables
• NEXT VALUE FOR seq_name to 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 VERSIONING to track all historical changes
• FOR SYSTEM_TIME AS OF '2024-01-01 00:00:00' queries past state
• FOR SYSTEM_TIME BETWEEN start AND end for change history
• Invisible columns row_start and row_end store validity period

JSON Handling

• JSON_VALUE(col, '$.key') extracts scalar, returns NULL if not found
• JSON_QUERY(col, '$.obj') extracts object/array with quotes preserved
• JSON_TABLE() converts JSON array to rows—powerful for unnesting
• JSON_VALID() before insert if column isn't strictly typed

Galera Cluster

• All nodes writable—but same-row conflicts cause rollback
• wsrep_sync_wait = 1 before critical reads—ensures node is synced
• Keep transactions small—large transactions increase conflict probability
• wsrep_cluster_size should be odd number—avoids split-brain

Window Functions

• ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) for ranking within groups
• LAG(col, 1) OVER (ORDER BY date) for previous row value
• SUM(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 UPDATE locks rows until commit
• LOCK TABLES t WRITE for DDL-like exclusive access—blocks all other sessions
• Deadlock detection automatic—one transaction rolled back; must retry
• innodb_lock_wait_timeout default 50s—lower for interactive apps

Query Optimization

• EXPLAIN ANALYZE for actual execution times (10.1+)
• optimizer_trace for deep dive: SET optimizer_trace='enabled=on'
• FORCE INDEX (idx) when optimizer chooses wrong index
• STRAIGHT_JOIN to force join order—last resort

Backup and Recovery

• mariadb-dump --single-transaction for consistent backup without locks
• mariadb-backup for 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_connections or 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