Index Types in MySQL

-- Primary key (clustered)
ALTER TABLE users ADD PRIMARY KEY (id);

-- Unique index
CREATE UNIQUE INDEX uk_email ON users(email);

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

-- Prefix index (for long strings)
CREATE INDEX idx_email_prefix ON users(email(10));

-- Fulltext index
CREATE FULLTEXT INDEX ft_content 
ON articles(title, content);

-- Spatial index
CREATE SPATIAL INDEX sp_location ON stores(location);

-- DETAILED_TAB:
-- Show indexes
SHOW INDEX FROM users;

-- Force/Ignore index
SELECT * FROM users 
USE INDEX (idx_email)
WHERE email = 'john@example.com';

SELECT * FROM users
IGNORE INDEX (idx_name)
WHERE last_name = 'Smith';

-- Invisible index (MySQL 8.0+)
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;

-- Descending index (MySQL 8.0+)
CREATE INDEX idx_created ON orders(created_at DESC);

-- Functional index (MySQL 8.0+)
CREATE INDEX idx_month 
ON sales((MONTH(sale_date)));