MySQL Index Strategies and Verification

Composite indexes, invisible indexes, descending indexes, generated-column indexing, and EXPLAIN workflows in MySQL.

View

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## MySQL index patterns

Create a composite index

CREATE INDEX idx_orders_status_created_at ON orders (status, created_at DESC);

# Support a filter plus order-by pattern.

Index a generated column

ALTER TABLE users
  ADD COLUMN email_lc VARCHAR(255) GENERATED ALWAYS AS (LOWER(email)) STORED,
  ADD INDEX idx_users_email_lc (email_lc);

# Practical route for function-based searches.

Create an invisible index

CREATE INDEX idx_orders_coupon_code ON orders (coupon_code) INVISIBLE;

# Test an index without making it visible to the optimizer by default.

Toggle index visibility

ALTER TABLE orders ALTER INDEX idx_orders_coupon_code VISIBLE;

# Hide or re-enable an index without dropping it.

## Verify usage

Explain a query

EXPLAIN SELECT id, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

# See whether the optimizer chooses your index.

Refresh table statistics

ANALYZE TABLE orders;

# Help the optimizer make better choices after major data changes.

Test with FORCE INDEX

SELECT id
FROM orders FORCE INDEX (idx_orders_status_created_at)
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

# Diagnostic tool for checking alternate index paths.

More in Database Indexing

No recommendations yet.