Engineering Playbook

MySQL Architecture and Indexing Strategies

At Verpex Solutions Pty Ltd, we believe that application performance is dictated by the database. No amount of compute power or horizontal scaling at the application layer can compensate for a poorly architected data layer.

1. The Philosophy of the Index

An index is not a silver bullet. Every index created speeds up SELECT operations but imposes a penalty on INSERT, UPDATE, and DELETE operations because the index tree must be rebuilt.

The Verpex Standard

  • Never index every column.
  • Index columns only if they are frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.
  • Prioritize indexing columns with high cardinality (a high number of unique values). Indexing a boolean column like is_active is generally an anti-pattern as it forces the database to scan large portions of the index anyway.

2. Mastering the InnoDB Clustered Index

In MySQL (InnoDB), the primary key is the clustered index. This means the actual row data is stored in the leaf nodes of the primary key index. All secondary indexes point back to the primary key, not the physical row.

The Verpex Standard

  • Use sequential integers for Primary Keys: We strongly prefer BIGINT AUTO_INCREMENT for primary keys. Sequential inserts prevent page fragmentation in the B-Tree structure.
  • Handle UUIDs with care: If a UUID is strictly required for distributed system uniqueness or security, we store it as a BINARY(16) rather than a VARCHAR(36) to save space and improve comparison speed. We still maintain an internal, auto-incrementing BIGINT as the actual primary key to keep the clustered index highly optimized.

3. Composite Indexes and the Leftmost Prefix Rule

When querying against multiple columns, a composite index is vastly superior to multiple single-column indexes. However, MySQL evaluates composite indexes strictly from left to right. If you create an index on (tenant_id, status, created_at):

  • A query filtering by tenant_id and status will use the index.
  • A query filtering by tenant_id only will use the index.
  • A query filtering by status only will bypass the index entirely, resulting in a full table scan.

The Verpex Standard

Always order composite index columns by equality first, then ranges. Place the most heavily queried and highest cardinality columns on the far left of the index definition.

4. Bridging the ORM Gap (Spring Boot & Hibernate)

Object-Relational Mapping tools are excellent for developer velocity but are the primary cause of enterprise database bottlenecks if left unchecked. The N+1 query problem is the most common silent killer of application efficiency.

The Verpex Standard

  • We rely on Spring Boot and Hibernate for rapid application development, but we actively profile the generated SQL.
  • Use JOIN FETCH or @EntityGraph in Spring Data JPA repositories to eagerly load required relationships in a single optimized query.
  • We never allow developers to rely on default ORM schema generation (spring.jpa.hibernate.ddl-auto=update) in production environments. All database migrations and index creations are strictly version-controlled using tools like Flyway or Liquibase.

5. Mandatory Query Profiling

We do not guess why a system is slow. We measure it. Every slow query identified in our AWS RDS performance insights must be run through the MySQL EXPLAIN statement before any code refactoring begins.

EXPLAIN SELECT id, first_name, last_name
FROM users
WHERE tenant_id = 405 AND last_login > '2026-01-26';

The Verpex Standard

Engineers must ensure that the type column in the EXPLAIN output avoids ALL (full table scans) and that the key column confirms the correct index is being utilized.