10 Essential MySQL Indexing Rules to Speed Up Queries23 min read

Few bottlenecks degrade web application performance faster than sluggish database lookups. When your datasets grow from thousands of rows into millions, unoptimized queries can quickly push CPU utilization to 100%, causing sluggish response times and driving impatient visitors away. Mastering these Essential MySQL Indexing Rules enables developers to transform painful full-table scans into instant index seeks, routinely accelerating slow queries by 90% or more without costly server hardware upgrades.

In this tutorial, you will explore the exact mechanics of MySQL B-Tree indexing, learn how to decode query execution plans with EXPLAIN, and master practical rules for production database tuning. We will examine worked SQL examples, contrast efficient designs with common anti-patterns, solve hands-on performance exercises, and review a quick-reference recap table to guide your database architecture decisions.

Tutorial Overview: Learning Goals and Prerequisites

Before optimizing queries, let us clarify the roadmap for this guide. High-performance indexing requires a clear understanding of data storage mechanics combined with practical query profiling habits.

• Learning Goals: Understand how InnoDB stores clustered and secondary B-Trees; master the Leftmost Prefix Rule for composite keys; build covering indexes to bypass table lookups; craft sargable query predicates; and diagnose optimizer decisions using EXPLAIN.
• Prerequisites: Basic experience writing SQL queries (SELECT, WHERE, JOIN, ORDER BY), access to a local or remote MySQL 8.0+ server, and standard privileges to run table schema modifications.
• Storage Engine Context: All examples in this tutorial focus on MySQL’s default storage engine, InnoDB, which structures records differently than legacy engines like MyISAM.

With these prerequisites in place, let us examine how InnoDB stores records on disk before examining the core rules.

Understanding Clustered Indexes vs. Secondary B-Trees

To design efficient indexes, you must understand how InnoDB stores data on physical storage media. InnoDB does not store rows in unordered heap files. Instead, it structures every table as an ordered B+Tree centered around the Primary Key.

InnoDB divides index structures into two fundamental types:

• Clustered Index: The primary key is the table. Leaf nodes in the clustered B-Tree contain all column values for each row. Querying by primary key retrieves the complete record in a single tree traversal.
• Secondary Index: Any index other than the primary key is a secondary index. In a secondary index, leaf nodes store only the indexed column values paired with the corresponding row’s primary key value.
• Bookmark Lookup: When a query requests columns not present in the secondary index, MySQL must use the found primary key to look up the remaining columns in the clustered index. This second step is called a bookmark lookup.

Executing thousands of bookmark lookups introduces substantial random disk I/O. Following sound indexing rules eliminates unnecessary table lookups and provides the optimizer with the fastest retrieval path.

1. Pick Sequential Primary Keys to Prevent B-Tree Page Splits

Your primary key strategy dictates the physical storage organization of your entire table. Because secondary indexes store the primary key in every leaf entry, wide or randomized primary keys degrade both storage density and write throughput.

Developers often mistakenly use random UUID v4 strings as primary keys:

-- Anti-pattern: Random UUID strings trigger expensive page splits
CREATE TABLE orders_fragmented (
    order_id VARCHAR(36) NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (order_id),
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB;

Because UUID v4 strings are generated randomly, new records must be inserted into arbitrary positions across existing 16KB InnoDB pages. When a target page is full, InnoDB must split it into two pages, leaving them partially empty and causing severe storage fragmentation. Furthermore, every secondary index must store that 36-character string in its leaf nodes.

Instead, adopt an auto-incrementing integer or sequential identifier:

-- Best Practice: Monotonically increasing primary key keeps pages dense
CREATE TABLE orders_optimized (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_uuid BINARY(16) NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_order_uuid (order_uuid),
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB;

An auto-incrementing integer ensures that new rows append sequentially to the end of the clustered index. This eliminates page splits, maximizes cache density in the InnoDB Buffer Pool, and slashes secondary index sizes by up to 60%.

2. Master the Leftmost Prefix Rule for Composite Indexes

Composite indexes contain multiple columns in a single index structure. However, MySQL can only search composite indexes from left to right, following the strict Leftmost Prefix Rule.

Think of a phone directory sorted first by Last Name, then by First Name, and finally by City. You can easily find everyone named “Smith”, or everyone named “Smith, Alice”. But if you only know the first name “Alice”, the phone book’s sorted order cannot help you find the record without scanning every single page.

-- Creating a three-column composite index
ALTER TABLE customer_orders 
ADD INDEX idx_tenant_status_date (tenant_id, status, created_at);

This single composite index can satisfy several distinct query filters:

• Fully Supported: Filtering by WHERE tenant_id = 101 uses the first column of the index.
• Fully Supported: Filtering by WHERE tenant_id = 101 AND status = 'PAID' uses the first two columns.
• Fully Supported: Filtering by WHERE tenant_id = 101 AND status = 'PAID' AND created_at >= '2026-01-01' uses all three columns.
• Unsupported: Filtering solely by WHERE status = 'PAID' or WHERE created_at >= '2026-01-01' cannot use this index because tenant_id is omitted, forcing a full table scan.

Always order composite index columns starting with the fields most consistently present across your application’s WHERE clauses.

3. Order Composite Columns: Equality First, Range Later

A widespread indexing mistake involves column ordering when mixing exact matches (=) with range conditions (>, <, BETWEEN, LIKE 'abc%'). Once MySQL encounters a range comparison on an indexed column, it cannot use any subsequent columns in that index for tree seeks.

Consider a frequent query where users filter records by customer, status, and transaction date:

-- Query filtering by exact customer, exact status, and date range
SELECT id, total_amount 
FROM orders 
WHERE customer_id = 4502 
  AND status = 'SHIPPED' 
  AND order_date >= '2026-10-01';

Observe how column sequence in your composite index directly changes query execution:

-- Suboptimal: Range column placed before equality column
ALTER TABLE orders ADD INDEX idx_bad (customer_id, order_date, status);

-- Highly Efficient: Equality columns placed before range column
ALTER TABLE orders ADD INDEX idx_good (customer_id, status, order_date);

With idx_bad, MySQL seeks on customer_id and performs a range scan on order_date. Because the date range spans multiple dates, the index cannot narrow down by status, forcing MySQL to inspect extra rows.

With idx_good, MySQL performs exact seeks on both customer_id and status before scanning the tightly bounded order_date range. This cuts examined rows dramatically.

4. Build Covering Indexes to Eliminate Bookmark Lookups

A covering index contains every column requested by a query, including columns in SELECT, WHERE, JOIN, and ORDER BY clauses. When a covering index exists, MySQL retrieves the entire result set directly from index memory without touching table rows on disk.

You can identify a covering index by looking for Using index in the Extra column of an EXPLAIN output:

-- Diagnostic check on a user query
EXPLAIN SELECT user_id, email, last_login 
FROM users 
WHERE department_id = 8 AND is_active = 1;

If your index only includes (department_id, is_active), MySQL must fetch the matching primary keys and perform clustered lookups to read email and last_login. Let us make the index covering:

-- Add requested columns to satisfy the query entirely within the index
ALTER TABLE users 
ADD INDEX idx_users_cover (department_id, is_active, user_id, email, last_login);

With this index in place, EXPLAIN reports Using index in the Extra field. The engine retrieves all data from memory buffers, reducing query execution times from hundreds of milliseconds to single digits.

5. Write Sargable Queries: Avoid Functions on Indexed Columns

A query predicate is sargable (Search Argument Able) if MySQL can evaluate it using an index seek. Wrapping indexed columns in functions, arithmetic expressions, or type conversions ruins sargability and forces a full table scan.

When an indexed column is wrapped in a function, MySQL must evaluate that function row by row for every single record in the table:

-- Non-sargable anti-pattern: Function prevents index range seek
SELECT id, total_amount 
FROM orders 
WHERE YEAR(created_at) = 2026;

-- Sargable rewrite: Preserves raw column and evaluates an explicit date range
SELECT id, total_amount 
FROM orders 
WHERE created_at >= '2026-01-01 00:00:00' 
  AND created_at < '2027-01-01 00:00:00';

The first query executes YEAR() on every row in the table, scanning millions of records. The second query isolates created_at, allowing MySQL to perform an instant B-Tree range seek on the indexed timestamp column.

Common non-sargable patterns include mathematical operations (WHERE balance * 1.05 > 500), date string parsing (WHERE DATE(event_time) = '2026-11-15'), and substring evaluations (WHERE LEFT(sku, 3) = 'CAT'). Always isolate the indexed column on one side of the operator.

6. Prevent Implicit Data Type Conversions

MySQL attempts to resolve comparisons between differing data types automatically. However, when an indexed string column is compared against a numeric literal, MySQL converts the table column to a number for every row, disabling index lookups.

Consider an authentication lookup on an indexed phone_number column defined as VARCHAR(20):

-- Dangerous anti-pattern: Numeric literal forces full table scan
SELECT id, full_name 
FROM members 
WHERE phone_number = 9876543210;

-- Correct: String literal enables instant index seek
SELECT id, full_name 
FROM members 
WHERE phone_number = '9876543210';

Because string-to-number casting rules can yield unexpected matches, MySQL converts the stored column value to a float rather than converting the search literal. This forces a full table scan across every row.

Similarly, joining tables with mismatched collations (such as joining utf8mb4_unicode_ci to utf8mb4_general_ci) prevents index lookups. Ensure foreign key columns share identical data types, lengths, and character collations.

7. Use Prefix Indexes for Wide String Columns

Indexing long VARCHAR columns—such as email addresses, URLs, or file paths—can inflate index size rapidly. Bulky indexes consume excessive Buffer Pool memory, reducing cache efficiency and increasing disk reads.

A prefix index stores only the first N characters of a string column, providing strong selectivity while saving significant index storage space:

-- Step 1: Evaluate distinct value ratios across various prefix lengths
SELECT 
    COUNT(DISTINCT email) AS total_distinct,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(DISTINCT email) AS ratio_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(DISTINCT email) AS ratio_15,
    COUNT(DISTINCT LEFT(email, 20)) / COUNT(DISTINCT email) AS ratio_20
FROM customers;

When a prefix length reaches a selectivity ratio of 0.98 or higher, 98% of values are distinct within that character limit. You can then create the index safely:

-- Step 2: Create a prefix index using the optimal character length
ALTER TABLE customers ADD INDEX idx_email_prefix (email(15));

Indexing 15 characters instead of the full 255-character column shrinks index storage by over 70% while maintaining fast search speeds. Keep in mind that prefix indexes cannot be used for ORDER BY or covering indexes because the full string is not stored in the index node.

8. Eliminate “Using Filesort” by Indexing ORDER BY and GROUP BY

When MySQL cannot read rows in the requested sort order directly from an index, it must collect the matching rows into temporary memory or disk files and sort them. In an EXPLAIN plan, this appears as Using filesort.

You can eliminate filesort operations by aligning your composite index columns with both your filter criteria and sorting direction:

-- Paginated catalog query with filtering and sorting
SELECT id, title, price, created_at 
FROM products 
WHERE category_id = 5 
ORDER BY created_at DESC 
LIMIT 20;

If you only index (category_id), MySQL gathers all products in category 5 and sorts them in memory. In contrast, look at what happens when you create a composite index:

-- Composite index matching both the filter and the sort direction
ALTER TABLE products ADD INDEX idx_cat_created (category_id, created_at DESC);

Because B-Trees store data in sorted order, MySQL reads category 5 rows directly in descending date order from index leaf nodes, stops immediately after 20 rows due to LIMIT, and eliminates the filesort step entirely.

9. Avoid Leading Wildcards in LIKE Queries

The placement of the wildcard symbol (%) in a SQL LIKE query determines whether MySQL can utilize an index seek or must fall back to a full table scan.

Compare leading, trailing, and middle wildcards:

-- Anti-pattern: Leading wildcard forces a full table scan
SELECT id, sku, product_name 
FROM inventory 
WHERE sku LIKE '%PROD-99';

-- Sargable: Trailing wildcard enables an index range seek
SELECT id, sku, product_name 
FROM inventory 
WHERE sku LIKE 'PROD-99%';

Because B-Tree nodes sort strings starting from the first character, searching for 'PROD-99%' allows MySQL to jump straight to the matching prefix and scan forward. A leading wildcard like '%PROD-99' provides no starting boundary, forcing a full table scan.

For arbitrary substring searches across large text columns, use a FULLTEXT index instead of standard B-Trees:

-- Implement FULLTEXT indexing for arbitrary search terms
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body);

-- Query using boolean natural language matching
SELECT id, title 
FROM articles 
WHERE MATCH(title, body) AGAINST('+mysql +indexing' IN BOOLEAN MODE);

Full-text indexes use inverted keyword lists rather than B-Trees, enabling fast text matching without scanning entire tables.

10. Audit, Prune Unused Indexes, and Track Write Costs

Every index added to a table speeds up read queries but introduces overhead on write operations. Whenever an INSERT, UPDATE, or DELETE executes, MySQL must update the clustered index and every secondary index on that table.

Over-indexed tables suffer from bloated storage, higher memory demands, and degraded write throughput. MySQL 8.0 provides built-in tools to identify unused indexes:

-- Identify unused indexes across your database using the sys schema
SELECT 
    object_schema AS database_name,
    object_name AS table_name,
    index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'my_production_db';

Before dropping an index in production, use MySQL 8.0’s Invisible Indexes feature. Making an index invisible hides it from the query optimizer while maintaining it in the background:

-- Step 1: Hide index from the optimizer to verify safe removal
ALTER TABLE customer_orders ALTER INDEX idx_old_reference INVISIBLE;

-- Step 2: Drop the index permanently once verified safe over 48 hours
ALTER TABLE customer_orders DROP INDEX idx_old_reference;

Invisible indexes provide a risk-free way to verify that dropping an index will not trigger slow query spikes in production.

How to Read and Interpret MySQL EXPLAIN Output

Profiling slow queries begins with EXPLAIN. Prepending EXPLAIN to any query reveals the execution plan chosen by the MySQL query optimizer.

-- Profile the execution plan for an order status lookup
EXPLAIN 
SELECT o.id, o.order_date, c.company_name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.status = 'PENDING' AND o.order_date >= '2026-11-01';

When inspecting the execution table, focus on these essential fields:

In MySQL 8.0+, run EXPLAIN ANALYZE to execute the query and view actual elapsed execution time per iterator alongside the optimizer’s initial estimates.

Summary Comparison: The 10 Indexing Rules at a Glance

Use this consolidated reference table to quickly review the core mechanism and primary benefit of each rule during database design and code reviews:

Practical Exercises: Test Your Knowledge

Reinforce your understanding of MySQL indexing mechanics with these two real-world database tuning challenges.

Exercise 1: Optimizing Filter and Sort on Support Tickets

You have a database table tracking support tickets with 3,000,000 rows. Users frequently view open tickets sorted by creation date within their department:

-- Slow Query (takes ~1.5 seconds)
SELECT id, subject, priority, created_at 
FROM support_tickets 
WHERE department_id = 12 
  AND status = 'OPEN' 
ORDER BY created_at DESC 
LIMIT 25;

A previous developer added this index: ADD INDEX idx_tickets_date (created_at, department_id, status). Running EXPLAIN reveals type: ALL or type: index with Using where; Using filesort.

Your Task: Explain why the existing index fails and define the optimal index structure.

Solution: The existing index violates the Leftmost Prefix Rule and places the sorted column before the equality filters. Because created_at is first, MySQL cannot jump directly to department_id = 12 without scanning the entire date tree. To fix this, place the equality columns first, followed by the sorting column:

-- Optimal Index
ALTER TABLE support_tickets 
ADD INDEX idx_dept_status_created (department_id, status, created_at DESC);

This allows MySQL to seek instantly to department 12 tickets with open status, read 25 records already in descending chronological order, and exit immediately with zero filesort overhead.

Exercise 2: Refactoring a Non-Sargable Date Query

A financial summary script runs slowly every night due to this query filter:

-- Unoptimized reporting query
SELECT SUM(amount) AS daily_revenue 
FROM transactions 
WHERE DATE_FORMAT(transaction_time, '%Y-%m-%d') = '2026-11-15';

The table has an index on (transaction_time), yet EXPLAIN shows type: ALL with 2,500,000 rows examined.

Your Task: Rewrite the query into a sargable expression without altering existing table indexes.

Solution: The DATE_FORMAT() function prevents MySQL from using the index on transaction_time. Rewrite the predicate as an explicit date range comparison:

-- Sargable rewrite using half-open time interval
SELECT SUM(amount) AS daily_revenue 
FROM transactions 
WHERE transaction_time >= '2026-11-15 00:00:00' 
  AND transaction_time < '2026-11-16 00:00:00';

This enables an index range scan (type: range) covering only transactions within that single 24-hour window, reducing examined rows from 2.5 million down to a few thousand.

Frequently Asked Questions: Essential MySQL Indexing Rules

How many indexes are too many for a single MySQL table?

Most high-throughput OLTP tables perform best with between 3 and 7 well-targeted composite indexes. Once you exceed 8 to 10 secondary indexes, the write penalty during INSERT, UPDATE, and DELETE operations begins to outweigh read gains. Monitor write latency and prune unused keys regularly using the sys schema.

Why is MySQL ignoring my index even though it exists?

The MySQL cost-based optimizer deliberately ignores an index if it calculates that a full table scan will be faster. This commonly occurs if your query condition matches more than 15% to 20% of the table’s total rows, or if the table is small enough to fit in a few pages. Running ANALYZE TABLE your_table_name; can resolve stale index statistics.

What is the difference between a Unique Index and a Primary Key?

In MySQL InnoDB, a table can only have one Primary Key, which defines the physical clustered index and forbids NULL values. A Unique Index enforces uniqueness but is implemented as a secondary index, and MySQL permits NULL entries in unique secondary indexes unless the column is explicitly defined as NOT NULL.

Can I index JSON columns directly in MySQL?

You cannot create a standard B-Tree index directly on an entire JSON column in MySQL. However, you can create a generated column that extracts a specific JSON path and place a B-Tree index on that generated column. Alternatively, MySQL 8.0.17+ supports Multi-Valued Indexes for indexing arrays inside JSON documents.

Does adding an index lock the table during production deployment?

In MySQL 8.0+, InnoDB supports Online DDL for most index creation operations using ALGORITHM=INPLACE, LOCK=NONE. This permits concurrent reads and writes while the index builds. However, always test DDL changes in staging first to ensure your specific environment avoids table locks.

Conclusion

Database performance tuning is a systematic engineering discipline governed by predictable data structures. Adopting these Essential MySQL Indexing Rules equips you to diagnose bottlenecks with precision, eliminate costly full table scans, and keep web applications fast and responsive as your datasets grow.

Your clear next step: open your terminal, enable the MySQL Slow Query Log, and run EXPLAIN on your application’s slowest queries today. Identify missing composite keys, rewrite non-sargable functions, and watch your query response times drop by 90%.