sql
CREATE INDEX idx_orders_status_created_at ON orders (status, created_at DESC);Notes
Descending indexes can help mixed sort-order workloads and order-sensitive queries in modern MySQL.
MySQL Index Strategies and Verification
Composite indexes, invisible indexes, descending indexes, generated-column indexing, and EXPLAIN workflows in MySQL.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
MySQL index patterns
Common MySQL DDL and tuning workflows.
Index a generated column
Practical route for function-based searches.
sqlANYmysqlgenerated-columnfunctional-index
sql
ALTER TABLE users
ADD COLUMN email_lc VARCHAR(255) GENERATED ALWAYS AS (LOWER(email)) STORED,
ADD INDEX idx_users_email_lc (email_lc);Notes
A generated column can make case-normalized lookups indexable in MySQL.
Create an invisible index
Test an index without making it visible to the optimizer by default.
sqlANYmysqlinvisible-index
sql
CREATE INDEX idx_orders_coupon_code ON orders (coupon_code) INVISIBLE;Notes
Invisible indexes are useful for experiments and staged rollouts.
sql
ALTER TABLE orders ALTER INDEX idx_orders_coupon_code VISIBLE;Notes
This lets you test optimizer behavior before committing to a drop.
Verify usage
Use EXPLAIN and related patterns before and after adding an index.
sql
EXPLAIN SELECT id, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;Notes
Look at the chosen key, access method, estimated rows, and extra information.
Refresh table statistics
Help the optimizer make better choices after major data changes.
sqlANYmysqlanalyze-tablestatistics
sql
ANALYZE TABLE orders;Notes
Stale statistics can cause unexpected plans even when a good index exists.
sql
SELECT id
FROM orders FORCE INDEX (idx_orders_status_created_at)
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;Notes
Useful for testing, but avoid relying on it as the first fix for poor design.
More in Database Indexing
Indexing Anti-Patterns and Maintenance
Avoid over-indexing, duplicate indexes, low-value indexes, stale statistics, and maintenance mistakes.
EXPLAIN, ANALYZE, and Verify Index Usage
Use EXPLAIN tools across PostgreSQL, MySQL, SQLite, and MongoDB to prove whether an index actually helps.
Partial, Expression, and Functional Indexes
High-leverage index patterns for subsets of data and computed predicates across PostgreSQL, MySQL, and SQLite.
MongoDB Index Types and Query Patterns
Single-field, compound, multikey, text, wildcard, partial, TTL, and unique index patterns for MongoDB.
SQLite Indexes and Query Planner
SQLite CREATE INDEX patterns, expression and partial indexes, EXPLAIN QUERY PLAN, and lightweight performance tuning.
PostgreSQL Index Types and Strategies
Use PostgreSQL B-tree, GIN, GiST, BRIN, partial indexes, expression indexes, and concurrent builds effectively.