#MySQL

-- Add unique index to existing table

-- Add index to existing table

-- For InnoDB and MyISAM tables

-- Use MATCH AGAINST for searching

-- Natural language mode

-- Boolean mode

-- Query using spatial functions

-- Composite index column order matters
-- Index can be used for:
-- - user_id alone
-- - user_id AND status
-- But NOT for status alone (without user_id)

    -- Indexes

-- Add simple index

-- Add unique index

-- Add composite index

-- Add full-text index (MySQL 5.6+)

-- Add spatial index

-- Custom index name

-- Show indexes on a table

-- Detailed index information

-- From information_schema

-- Drop index

-- Drop multiple indexes

-- Using ALTER TABLE

-- Use EXPLAIN to see query execution

-- EXPLAIN output columns
-- id: Query identifier
-- select_type: Type of SELECT
-- table: Table being accessed
-- type: How rows are found (ALL, index, range, etc.)
-- possible_keys: Indexes available
-- key: Index actually used
-- key_len: Length of key used
-- rows: Rows examined
-- Extra: Additional information

-- Check index cardinality (unique values)

-- High cardinality: Many unique values (good for indexing)
-- Low cardinality: Few unique values (less effective)

-- Index columns used in WHERE clauses

-- Index columns used in JOINs

-- Index ORDER BY columns

-- Index columns in GROUP BY

-- Consider query patterns
-- Query: WHERE user_id = ? AND status = ?
-- Should have index on (user_id, status)

-- Left-to-right rule
-- Index supports queries starting from left columns

-- Can be used for:
-- WHERE user_id = 5
-- WHERE user_id = 5 AND status = 'completed'
-- But NOT for WHERE status = 'completed' alone

-- Index contains all columns needed by query
-- Query doesn't need to access main table

-- Create covering index

-- This query uses covering index

-- EXPLAIN shows "Using index" in Extra column

-- Index on first N characters of string column
-- Useful for long text columns with repetitive prefixes

-- Create prefix index

-- Choose appropriate prefix length

-- Don't index small tables
-- Don't index columns with low cardinality (few unique values)
-- Don't index columns frequently updated
-- Don't index columns with long text values (use prefix index)

-- Search modes
-- Natural language (default)

-- Boolean mode

-- Query expansion

-- Spatial queries

-- Update index cardinality

-- Check table analysis

-- Defragment table and reclaim unused space

-- Helps with:
-- - Deleted rows
-- - Split pages
-- - Index rebuilding

-- Rebuild specific index

-- Rebuild all indexes

-- Identify unused indexes (MySQL 8.0+)

-- Create indexes for:
-- 1. Primary keys (automatic)
-- 2. Foreign keys (automatic for InnoDB)
-- 3. Columns in WHERE clauses
-- 4. Columns in JOIN conditions
-- 5. Columns in ORDER BY
-- 6. Columns in GROUP BY
-- 7. Columns for uniqueness

-- Avoid indexes for:
-- 1. Tables with few rows
-- 2. Columns with low cardinality (few unique values)
-- 3. Columns frequently updated
-- 4. Columns with long text values (use prefix)
-- 5. Columns only in SELECT, not WHERE/JOIN/ORDER BY

-- Enable performance schema

-- Check slow queries

-- Use EXPLAIN regularly