MariaDB

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