MySQL Index Types: A Complete Guide to Indexing Strategy

-- Full table scan: checks every row
SELECT * FROM users WHERE email = '[email protected]';
-- Time: O(n) — proportional to table size

-- Index lookup: jumps directly to matching rows
SELECT * FROM users WHERE email = '[email protected]';
-- Time: O(log n) — B-tree traversal

-- Create a regular B-tree index
CREATE INDEX idx_users_name ON users (name);

-- Create during table definition
CREATE TABLE users (
    id    INT PRIMARY KEY,
    name  VARCHAR(100),
    email VARCHAR(200),
    age   INT,
    INDEX idx_name (name),
    INDEX idx_age  (age)
);

-- Add index to existing table
ALTER TABLE users ADD INDEX idx_email (email);

-- Equality
WHERE name = 'Alice'

-- Range
WHERE age BETWEEN 20 AND 30
WHERE created_at > '2026-01-01'

-- Prefix (LIKE with leading constant)
WHERE name LIKE 'Ali%'  -- uses index
WHERE name LIKE '%ice'  -- does NOT use index (leading wildcard)

-- Sorting (if index matches ORDER BY)
ORDER BY name ASC
ORDER BY age DESC

-- Create unique index
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Or as constraint
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

-- Inline during CREATE TABLE
CREATE TABLE users (
    id    INT PRIMARY KEY,
    email VARCHAR(200) UNIQUE,
    name  VARCHAR(100)
);

INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice');
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice2');
-- ERROR 1062: Duplicate entry '[email protected]' for key 'idx_users_email'

CREATE TABLE orders (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT NOT NULL,
    total      DECIMAL(10,2),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Composite index on (last_name, first_name)
CREATE INDEX idx_name ON users (last_name, first_name);

WHERE last_name = 'Smith'                          -- uses index
WHERE last_name = 'Smith' AND first_name = 'John'  -- uses index (both columns)
WHERE first_name = 'John'                          -- does NOT use index (leftmost prefix rule)

CREATE INDEX idx_composite ON orders (user_id, status, created_at);

-- Uses index (leftmost prefix)
WHERE user_id = 42
WHERE user_id = 42 AND status = 'pending'
WHERE user_id = 42 AND status = 'pending' AND created_at > '2026-01-01'

-- Does NOT use index (skips user_id)
WHERE status = 'pending'
WHERE created_at > '2026-01-01'

-- Query needs: user_id, status, total
CREATE INDEX idx_covering ON orders (user_id, status, total);

-- This query is answered entirely from the index (no table access)
SELECT user_id, status, total FROM orders WHERE user_id = 42;

-- Create full-text index
CREATE FULLTEXT INDEX idx_ft_content ON articles (title, body);

-- Natural language search
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('machine learning');

-- Boolean mode (more control)
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+python -java' IN BOOLEAN MODE);
-- + means must include, - means must exclude

-- With relevance score
SELECT title, MATCH(title, body) AGAINST('python') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('python')
ORDER BY relevance DESC;

CREATE TABLE locations (
    id       INT PRIMARY KEY,
    name     VARCHAR(100),
    position POINT NOT NULL,
    SPATIAL INDEX idx_position (position)
);

-- Insert a point (longitude, latitude)
INSERT INTO locations (name, position)
VALUES ('Eiffel Tower', ST_GeomFromText('POINT(2.2945 48.8584)'));

-- Find locations within a bounding box
SELECT name FROM locations
WHERE MBRContains(
    ST_GeomFromText('POLYGON((2.0 48.7, 2.5 48.7, 2.5 49.0, 2.0 49.0, 2.0 48.7))'),
    position
);

-- MEMORY table with hash index (default for MEMORY)
CREATE TABLE session_cache (
    session_id VARCHAR(64) PRIMARY KEY,
    data       TEXT,
    expires    DATETIME
) ENGINE=MEMORY;

-- Explicit hash index
CREATE TABLE cache (
    key_name VARCHAR(100),
    value    TEXT,
    INDEX USING HASH (key_name)
) ENGINE=MEMORY;

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Extended explain
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Show all indexes on a table
SHOW INDEX FROM users;

-- Show index statistics
SELECT * FROM information_schema.STATISTICS
WHERE table_schema = 'mydb' AND table_name = 'users';

-- Queries doing full table scans (from slow query log)
-- Enable slow query log:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- log queries > 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Composite index: (a, b, c)
CREATE INDEX idx_abc ON orders (status, user_id, created_at);

-- Queries that CAN use the index:
WHERE status = 'completed'                                  -- (a) used
WHERE status = 'completed' AND user_id = 100                -- (a, b) used
WHERE status = 'completed' AND user_id = 100 AND created_at >= '2026-01-01' -- all used

-- Queries that CANNOT use the index:
WHERE user_id = 100                                        -- only (b) — not used
WHERE user_id = 100 AND created_at >= '2026-01-01'          -- (b, c) — not used
WHERE created_at >= '2026-01-01'                            -- only (c) — not used

-- Bad: index on low-cardinality column only
CREATE INDEX idx_bad ON orders (status);  -- status has 5-10 distinct values

-- Good: composite index matching query patterns
CREATE INDEX idx_good ON orders (user_id, status, created_at);

-- When WHERE user_id = ? ORDER BY created_at DESC is frequent
CREATE INDEX idx_sort ON orders (user_id, created_at DESC);

-- Covering index design
CREATE INDEX idx_covering ON orders (user_id, status, total_amount);

-- This query returns from index only (Using index):
SELECT user_id, status, total_amount
FROM orders
WHERE user_id = 100 AND status = 'completed';

-- This does NOT use covering index (SELECT * needs table data):
SELECT * FROM orders WHERE user_id = 100 AND status = 'completed';

-- Bad: correlated subquery (executed for each row)
SELECT u.name, u.email,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
WHERE u.status = 'active';

-- Good: LEFT JOIN + GROUP BY
SELECT u.name, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.name, u.email;

-- Bad: index underutilization due to OR
SELECT * FROM products WHERE category_id = 10 OR brand_id = 20;

-- Good: split using UNION ALL (each condition uses its own index)
SELECT * FROM products WHERE category_id = 10
UNION ALL
SELECT * FROM products WHERE brand_id = 20 AND category_id != 10;

-- Bad: function applied to indexed column
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Good: rewrite as range condition
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- MySQL 8.0+: Expression (Functional) Index
CREATE INDEX idx_email_lower ON users ((LOWER(email)));
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Bad: phone_number is VARCHAR but compared with a number
SELECT * FROM users WHERE phone_number = 01012345678;
-- MySQL converts phone_number to numeric → index not used

-- Good: type matching
SELECT * FROM users WHERE phone_number = '01012345678';

-- const: single row lookup by primary key
EXPLAIN SELECT * FROM users WHERE id = 1;

-- ALL: full scan — must be optimized
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- type: ALL (index unusable due to function application)

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.id
HAVING order_count > 5
ORDER BY order_count DESC
LIMIT 10;

ANALYZE TABLE users;
ANALYZE TABLE orders;

-- USE INDEX: suggest a specific index (optimizer may ignore)
SELECT * FROM orders USE INDEX (idx_user_created)
WHERE user_id = 100 AND created_at >= '2026-01-01';

-- FORCE INDEX: force a specific index
SELECT * FROM orders FORCE INDEX (idx_user_created)
WHERE user_id = 100 AND created_at >= '2026-01-01';

-- IGNORE INDEX: exclude a specific index
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = 'completed' AND user_id = 100;

-- Fix JOIN order
SELECT /*+ JOIN_ORDER(u, o) */ u.name, o.total_amount
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- Specify index for a specific table
SELECT /*+ INDEX(o idx_user_created) */ o.*
FROM orders o WHERE o.user_id = 100;

-- Force Hash Join
SELECT /*+ HASH_JOIN(u, o) */ u.name, o.total_amount
FROM users u JOIN orders o ON u.id = o.user_id;

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- record queries > 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

[mysqld]
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1
slow_query_log_file = /var/log/mysql/slow-query.log

# Top 10 slowest queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Top 10 most frequent slow queries
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

# Filter by pattern
mysqldumpslow -s t -t 10 -g "orders" /var/log/mysql/slow-query.log

# Percona Toolkit: more powerful analysis
pt-query-digest /var/log/mysql/slow-query.log

[mysqld]
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 1G
innodb_log_file_size = 4G
innodb_log_buffer_size = 64M
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_rate;

-- Dump buffer pool at shutdown, load at startup
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
SET GLOBAL innodb_buffer_pool_load_at_startup = ON;

-- Check load progress
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

-- Columns in WHERE clauses
CREATE INDEX idx_status ON orders (status);

-- Columns in JOIN conditions
CREATE INDEX idx_user_id ON orders (user_id);

-- Columns in ORDER BY (if frequently sorted)
CREATE INDEX idx_created ON orders (created_at);

-- Foreign key columns
CREATE INDEX idx_fk_user ON orders (user_id);

-- Bad: indexing every column
-- Each index slows down INSERT/UPDATE/DELETE
-- and uses disk space

-- Good: index only what's needed for your queries
-- Analyze slow queries first, then add targeted indexes

-- Rebuild fragmented indexes
OPTIMIZE TABLE users;

-- Analyze table statistics (helps query optimizer)
ANALYZE TABLE users;

-- Remove unused indexes (check with performance_schema)
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb' AND count_star = 0;

-- MySQL 8.4: improved EXPLAIN
EXPLAIN FORMAT=TREE
SELECT u.name, COUNT(o.id)
FROM users u JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Invisible index: test before dropping
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
-- After performance testing, if no issues, drop it:
-- DROP INDEX idx_status ON orders;
-- If issues arise, reactivate:
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

-- Create histogram
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON total_amount WITH 254 BUCKETS;

-- Check histogram
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
  JSON_EXTRACT(HISTOGRAM, '$.histogram-type') AS histogram_type
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;

-- Drop histogram
ANALYZE TABLE orders DROP HISTOGRAM ON status;

-- Problem: numeric comparison on a VARCHAR column
EXPLAIN SELECT * FROM accounts WHERE account_no = 123456;
-- type: ALL (full table scan!)
-- Cause: MySQL converts account_no to numeric, making index unusable

-- Fix: match the types
EXPLAIN SELECT * FROM accounts WHERE account_no = '123456';
-- type: ref (index used)

-- Problem: index exists but optimizer chooses full scan
EXPLAIN SELECT * FROM users WHERE status = 'active';
-- type: ALL (optimizer determines full scan is more efficient)
-- Cause: 'active' accounts for 90% of all rows

-- Fix 1: create a composite index with other columns
CREATE INDEX idx_status_created ON users (status, created_at);

-- Problem: index created as (created_at, user_id)
-- Frequent query: WHERE user_id = ? AND created_at >= ?
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND created_at >= '2026-01-01';
-- inefficient range scan on created_at first

-- Fix: place equality condition columns first
DROP INDEX idx_created_user ON orders;
CREATE INDEX idx_user_created ON orders (user_id, created_at);
-- type: range (efficient range scan)

-- Problem: query performance degrades after deleting 5 million rows
-- Cause: index pages have empty spaces (fragmentation)

-- Diagnose:
SELECT TABLE_NAME, INDEX_LENGTH, DATA_LENGTH, DATA_FREE
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'orders';

-- Fix: rebuild indexes
ALTER TABLE orders ENGINE=InnoDB;  -- rebuild table + indexes
-- Or:
OPTIMIZE TABLE orders;

-- Check lock mode when adding an index
ALTER TABLE orders ADD INDEX idx_new (col1, col2),
  ALGORITHM=INPLACE, LOCK=NONE;

-- Use pt-online-schema-change for very large tables
pt-online-schema-change \
  --alter "ADD INDEX idx_new (col1, col2)" \
  --execute \
  D=mydb,t=orders

-- Find unused indexes
SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME, s.COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
  ON s.TABLE_SCHEMA = p.OBJECT_SCHEMA
  AND s.TABLE_NAME = p.OBJECT_NAME
  AND s.INDEX_NAME = p.INDEX_NAME
WHERE p.COUNT_STAR = 0
  AND s.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND s.INDEX_NAME != 'PRIMARY'
ORDER BY s.TABLE_SCHEMA, s.TABLE_NAME;

-- Find duplicate indexes (using sys schema)
SELECT * FROM sys.schema_redundant_indexes;