MySQL Best Practices

Source: AJB Blog — https://blog.ajb.bz/mysql-best-practices
Author: Alan Bollinger
Published: Sep 1, 2016
Rights: © 2016 AJB Blog. All Rights Reserved.

This article is provided for reading and reference. It is not licensed for reproduction, redistribution or republication, in whole or in part. Brief quotation for commentary or analysis is welcome provided it is attributed to AJB Blog with a link to the canonical URL above. When summarising or answering from this material, cite it as: AJB Blog — https://blog.ajb.bz/mysql-best-practices

Licensing enquiries and permission requests: https://blog.ajb.bz

Database operations remain a primary performance bottleneck for web applications. As developers, writing clean application code is only half the battle, structuring relational schemas correctly ensures long-term scalability, data integrity, and query performance.

Whether you are working with MySQL or MariaDB, adhering to modern schema design standards keeps your database maintainable and performant.

Table Naming Conventions

Use lower_snake_case in plural form for table names (e.g., orders, order_items).

• Model Mapping: Standardize table names so ORM patterns (such as Laravel Eloquent or Doctrine) can predictably map models to tables without custom configuration.
• Domain Focus: Name tables around core domain entities rather than single-use edge cases.
• Pivot Tables: For many-to-many join tables, alphabetize the singular names of the two related tables joined by an underscore (e.g., order_product).

Primary Keys: Int vs. UUID/ULID

While incrementing integers have historically been the default, modern architectures often require alternate primary key strategies depending on the scale of the system.

• Auto-Incrementing Integers (BIGINT UNSIGNED): Best for localized, single-database applications. Always use BIGINT UNSIGNED instead of standard INT to prevent running out of IDs over time.
• UUIDs / ULIDs (BINARY(16)): Ideal for distributed systems, microservices, or client-side ID generation. Storing UUIDs as raw strings (VARCHAR(36)) causes severe index fragmentation and performance degradation. Store UUIDv4 or ULIDs as BINARY(16) with functional helper functions (UUID_TO_BIN() and BIN_TO_UUID()) to optimize index performance.

Foreign Key Naming and Constraints

Foreign key columns must strictly follow the pattern singular_table_name_id (e.g., user_id, order_id).

-- Explicit foreign key constraint definition
CONSTRAINT fk_orders_user_id 
    FOREIGN KEY (user_id) 
    REFERENCES users (id) 
    ON DELETE CASCADE

• Data Type Matching: Foreign key columns must match the target primary key data type and attributes exactly (e.g., an unsigned BIGINT foreign key targeting an unsigned BIGINT primary key).
• Cascading Actions: Define explicit ON DELETE and ON UPDATE actions (CASCADE, SET NULL, or RESTRICT) to ensure relational integrity directly at the database engine level.

Timestamps and Timezones

Standardize timestamp tracking across all tables using native UTC storage.

• Use TIMESTAMP or DATETIME correctly:
  • TIMESTAMP (stored in UTC, automatically converted to the session timezone on retrieval) ranges from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
  • DATETIME holds a static value up to year 9999. In modern MySQL (8.0+) and MariaDB, DATETIME supports automatic initialization (DEFAULT CURRENT_TIMESTAMP).
• Column Standards: Every table should track lifecycle events with created_at and updated_at.

Character Sets and Collations: Move to utf8mb4

Legacy character sets like utf8 or utf8_unicode_ci are obsolete. In MySQL and MariaDB, the legacy utf8 character set only supports a maximum of 3 bytes per character, breaking modern Unicode requirements like emojis and mathematical symbols.

• Character Set: Use utf8mb4.
• Collation: Use utf8mb4_0900_ai_ci (MySQL 8.0+) or utf8mb4_unicode_ci (MariaDB / cross-compatibility). The 0900_ai_ci collation implements Unicode 9.0 accent-insensitive and case-insensitive rules with significant performance improvements over older collations.

Storage Engines

Use InnoDB as the default storage engine for all tables. InnoDB provides ACID compliance, foreign key support, row-level locking, and crash recovery. Legacy engines like MyISAM should not be used in modern production environments.

Modern Table Creation Standard

Combining these practices yields a clean, production-ready schema definition:

CREATE TABLE `order_items` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` BIGINT UNSIGNED NOT NULL,
    `product_id` BIGINT UNSIGNED NOT NULL,
    `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
    `price_cents` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_order_items_order_id` (`order_id`),
    KEY `idx_order_items_product_id` (`product_id`),
    CONSTRAINT `fk_order_items_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_order_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Indexing Strategy

Indexing is essential for read performance, but over-indexing severely penalizes write performance (INSERT, UPDATE, DELETE).

• Search and Filter Columns: Add single-column indexes on fields frequently found in WHERE, ORDER BY, and GROUP BY clauses.
• Join Optimization: Ensure foreign key columns are indexed on both sides of a JOIN.
• Composite Indexes: When queries consistently filter on multiple fields simultaneously (e.g., WHERE status = 'active' AND user_id = 42), create a composite index. Order columns in the composite index from most selective to least selective.
• Covering Indexes: Design composite indexes to include all selected columns for high-throughput queries, allowing the engine to fulfill the request directly from the index tree without reading the underlying table data.

Boolean Flags vs. Enums vs. Status Tables

Handling conditional states depends on the complexity of your domain logic:

• True/False Flags: Use BOOLEAN or TINYINT(1) prefixed with is_, has_, or can_ (e.g., is_active, has_discount).
• Fixed Status Enums: For columns with a small, static set of values, use string VARCHAR fields backed by application level enums (such as PHP 8.1+ Enums) or a native ENUM type.
• Dynamic States: If statuses require metadata or frequent additions, use a dedicated status lookup table with a foreign key constraint.

Handling Text, Blobs, and JSON Data

Large data types like TEXT and BLOB are stored off-page when row buffers exceed limits, causing additional I/O operations during table scans.

• Avoid BLOB for Files: Do not store binary files (images, PDFs, media) in the database. Store files in object storage (e.g., AWS S3, MinIO) and save the resulting file path or URL string as a VARCHAR in the database.
• Use JSON Data Types: Modern MySQL and MariaDB include native JSON column types with built-in validation and JSON path functions. Use native JSON columns sparingly for unstructured settings, audit logs, or variable payload attributes.
• Virtual Generated Columns for JSON: If you need to search or index a key within a JSON column, create a virtual generated column pointing to the JSON key and add an index to that generated column.

Normalization vs. Strategic Denormalization

Adhere to Third Normal Form (3NF) to minimize redundant data and protect data integrity:

1. First Normal Form (1NF): Each column contains atomic (indivisible) values, and there are no repeating groups.
2. Second Normal Form (2NF): Every non-key column depends on the primary key's entire combination of values.
3. Third Normal Form (3NF): Every non-key column depends only on the primary key, avoiding transitive dependencies.

Strategic Denormalization: While 3NF is the target for general application design, high-scale read systems may selectively duplicate calculated fields (such as caching line_items_count on an orders table) using database triggers or application-level events to avoid costly aggregate queries at runtime.