Mysql

Indexing

• Add indexes on all columns used in WHERE, JOIN, ORDER BY, and GROUP BY.
• Use composite indexes when filtering on multiple columns; order by selectivity (most selective first).
• Avoid indexing low-cardinality columns (e.g., boolean flags) in isolation.
• Use EXPLAIN to inspect query execution plans before deploying complex queries.
• Avoid full table scans on large tables.
• Use covering indexes (include all columns needed by a query) to avoid table lookups.

Soft Delete Pattern

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = ?;

-- Always filter in queries
SELECT * FROM users WHERE deleted_at IS NULL;

Always filter WHERE deleted_at IS NULL in application queries.

Migrations

• Use your framework's migration tool (Rails db:migrate, Flyway, Liquibase, etc.).
• Never apply schema changes directly to production without a migration file.
• Make migrations reversible where possible (add rollback/down steps).
• Add indexes in the same migration that adds the column.
• Schema changes require team lead approval.

Performance

• Avoid SELECT * — name the columns you need.
• Avoid functions on indexed columns in WHERE (prevents index use).
• Use LIMIT + OFFSET (or keyset pagination) for large result sets — never fetch all rows and filter in code.
• Use EXISTS instead of IN with large subqueries.
• Use INSERT ... ON DUPLICATE KEY UPDATE for upsert operations.
• Batch large UPDATE/DELETE operations to avoid long locks:

  DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 1000;
  

• Commit in batches for large DML to reduce lock contention.

Transactions

• Wrap multi-statement DML in explicit transactions (START TRANSACTION / COMMIT / ROLLBACK).
• Keep transactions short to minimize lock duration.
• Use appropriate isolation level (READ COMMITTED is usually sufficient; avoid SERIALIZABLE unless required).

Security

• Use prepared statements / parameterized queries everywhere — SQL injection prevention.
• Grant minimum necessary privileges (SELECT, INSERT, UPDATE on specific tables — never SUPER or ALL PRIVILEGES to app users).
• Never store plain-text passwords; hash with bcrypt (application layer) or use SHA2 only as a last resort.
• Disable remote root login; use dedicated app DB users.
• Enable require_secure_transport (TLS) for remote connections in production.
• Audit sensitive table access where required.
• Strip sensitive data from logs and error messages.

Naming Conventions

MariaDB-Specific Notes

• MariaDB is API-compatible with MySQL; most patterns apply equally.
• MariaDB supports FOR UPDATE SKIP LOCKED (10.6+) for queue-style processing.
• Use SEQUENCE objects (MariaDB 10.3+) as an alternative to AUTO_INCREMENT for non-PK sequences.
• MariaDB's JSON support differs slightly from MySQL 8.x — test JSON functions if migrating.