skills.rest
stefanfaur
stefanfaurCOMMUNITY
Explore all skills by this author →
GitHub

mariadb-best-practices

Guides MariaDB schema design, indexing, query optimization, and Hibernate integration using reference documentation.

1|Updated Feb 10, 2026
Search Tags:#query optimization#indexing#schema design#database#sql#mariadb#hibernate
One-click install
npx skills add https://github.com/stefanfaur/roach-marketplace --skill mariadb-best-practices-stefanfaur
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: mariadb-best-practices
Source: https://github.com/stefanfaur/roach-marketplace/tree/main/mariadb-mcp/skills/mariadb-best-practices
Command: npx skills add https://github.com/stefanfaur/roach-marketplace --skill mariadb-best-practices-stefanfaur

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Working with MariaDB involves subtle pitfalls like collation mismatches that silently disable indexes, implicit type conversions causing full table scans, and utf8/utf8mb4 confusion that truncates data. This Skill routes your database task to the right in-depth reference document and validates recommendations against the actual database state when MariaDB MCP tools are available. ## Core Features & Use Cases - Task-Based Routing: Directs schema design, indexing, query optimization, performance tuning, security, backup/replication, and migration questions to dedicated reference documents. - Hidden Gotcha Detection: Flags critical traps like collation mismatches on JOINs, implicit type conversions bypassing indexes, silent data truncation without strict mode, and AUTO_INCREMENT gaps. - Hibernate/QueryDSL Integration: Covers dialect selection, entity mapping, N+1 prevention, batch processing, HikariCP pooling, and QueryDSL patterns for Java applications. - Use Case: A developer notices a slow JOIN query. The Skill checks collation compatibility across joined columns, runs EXPLAIN ANALYZE via MariaDB MCP, and recommends a covering index following the equality-then-range column ordering rules. ## Quick Start Ask the assistant to review your MariaDB schema or diagnose a slow query, mentioning details like the table definitions, the query text, and your MariaDB version.

Frequently Asked Questions about mariadb-best-practices

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I optimize a slow MariaDB query?▼

Start by running EXPLAIN or EXPLAIN ANALYZE on the query to see the access type and rows examined. Check for full table scans (type ALL), verify indexes exist on filtered columns, and confirm collations match across joined columns since mismatches silently disable index usage.

How do I design a composite index in MariaDB?▼

Place columns with equality conditions first, then add one range condition or the GROUP BY/ORDER BY columns, and stop there. An index on (col1, col2, col3) only serves queries filtering from the leftmost prefix, so column order in the index matters more than WHERE clause order.

Should I use utf8 or utf8mb4 in MariaDB?▼

Always use utf8mb4. In MariaDB, utf8 is an alias for utf8mb3, which only stores up to 3-byte characters and silently truncates 4-byte characters like emoji and some CJK symbols. Note that utf8mb4 reduces the maximum indexable VARCHAR length under the COMPACT row format.

Which Hibernate dialect should I use for MariaDB?▼

Use a MariaDB-specific dialect such as org.hibernate.dialect.MariaDBDialect or a version-specific variant like MariaDB103Dialect, not a MySQL dialect. The wrong dialect generates incorrect DDL and misses MariaDB features like sequences and skip-locked row locking.

Why is my MariaDB index not being used in a JOIN?▼

The most common cause is a collation mismatch between the joined columns, which forces MariaDB to convert values and bypass the index. Implicit type conversion, such as comparing a VARCHAR column to a numeric literal, produces the same effect. Verify with SHOW CREATE TABLE and EXPLAIN.

When should I use mariadb-backup versus mariadb-dump?▼

Use mariadb-backup for large databases needing fast, nearly non-blocking physical backups with point-in-time recovery. Use mariadb-dump for smaller databases, selective table backups, or when you need portable SQL output across platforms or versions.

Related Skills

Explore More Software Engineering Skills→
clideyclidey
5.0k

whodb

Manage database connections, run queries, and explore schemas across multiple engines.

Official
Intermediate
congiuluccongiuluc

database-sqlite

Configure SQLite pragmas, WAL mode, indexing, and migrations for .NET applications.

Community
Advanced
OmniNode-aiOmniNode-ai
3

check-database-health

Monitor PostgreSQL database health, table statistics, connection pools, and query performance.

Official
Intermediate
kube-dckube-dc
34

create-database

Provision PostgreSQL or MariaDB databases with replicas and credential secrets in Kube-DC projects.

Official
Advanced