How to Create and Use Indexes

-- Create table without indexes
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT,
  email TEXT,
  age INTEGER,
  city TEXT
);

-- Insert sample data
INSERT INTO users (username, email, age, city) VALUES
  ('alice', 'alice@example.com', 25, 'New York'),
  ('bob', 'bob@example.com', 30, 'Los Angeles'),
  ('charlie', 'charlie@example.com', 35, 'Chicago'),
  ('diana', 'diana@example.com', 28, 'New York'),
  ('eve', 'eve@example.com', 32, 'Los Angeles');

-- Query without index (slower on large tables)
SELECT * FROM users WHERE email = 'alice@example.com';

-- Create index on frequently queried column
CREATE INDEX idx_users_email ON users(email);

-- Create index on multiple columns
CREATE INDEX idx_users_city_age ON users(city, age);

-- Query now uses index (faster)
SELECT * FROM users WHERE email = 'bob@example.com';

-- Query using composite index
SELECT * FROM users WHERE city = 'New York' AND age > 25;

-- View indexes (SQLite specific)
SELECT name, tbl_name FROM sqlite_master WHERE type = 'index';

-- Drop index
DROP INDEX idx_users_email;

-- Unique index (prevents duplicates)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- This will fail (duplicate username)
-- INSERT INTO users (username, email, age, city) 
-- VALUES ('alice', 'alice2@example.com', 26, 'Boston');

-- Partial index (only index certain rows)
CREATE INDEX idx_users_young ON users(age) WHERE age < 30;

-- Query uses partial index
SELECT * FROM users WHERE age < 30;

-- Covering index (includes all needed columns)
CREATE INDEX idx_users_covering ON users(city, age, username);

-- This query can be satisfied entirely from index
SELECT city, age, username FROM users WHERE city = 'Chicago';

-- Best practices:
-- 1. Index columns used in WHERE, JOIN, ORDER BY
-- 2. Don't over-index (slows down INSERT/UPDATE)
-- 3. Use composite indexes for multi-column queries
-- 4. Consider partial indexes for large tables

SELECT * FROM users;