Lesson 8: Fix the Slow Book Search

-- Create our book database
CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    author VARCHAR(255),
    isbn VARCHAR(13),
    genre VARCHAR(50),
    publication_year INT,
    rating DECIMAL(3,2),
    price DECIMAL(10,2)
);

-- Insert 100,000 books (this will take a minute!)
INSERT INTO books (title, author, isbn, genre, publication_year, rating, price)
SELECT 
    CONCAT('Book ', n),
    CONCAT('Author ', FLOOR(n/10)),
    LPAD(n, 13, '0'),
    CASE 
        WHEN n % 5 = 0 THEN 'Fiction'
        WHEN n % 5 = 1 THEN 'Mystery'
        WHEN n % 5 = 2 THEN 'Science'
        WHEN n % 5 = 3 THEN 'History'
        ELSE 'Fantasy'
    END,
    1900 + (n % 124),
    ROUND(1 + (RAND() * 4), 2),
    ROUND(9.99 + (RAND() * 90), 2)
FROM (
    SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + 1 as n
    FROM 
        (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
        (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
        (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c,
        (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d,
        (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e
    LIMIT 100000
) numbers;

-- Now try searching (THIS WILL BE SLOW!)
SELECT * FROM books WHERE author = 'Author 500';
-- ⏱️ This takes 5+ seconds!

-- Check how MySQL executes our slow query
EXPLAIN SELECT * FROM books WHERE author = 'Author 500';

-- Result shows:
-- type: ALL (Full table scan! 😱)
-- rows: ~100,000 (Checking EVERY book!)
-- Extra: Using where

-- Turn on profiling
SET profiling = 1;

-- Run our slow query
SELECT * FROM books WHERE author = 'Author 500';

-- See the time
SHOW PROFILES;
-- Query took: 5.2 seconds 🐌

-- Create an index on author
CREATE INDEX idx_author ON books(author);

-- Now try the same search
SELECT * FROM books WHERE author = 'Author 500';
-- ⚡ Only 0.05 seconds!

-- Let's see what changed
EXPLAIN SELECT * FROM books WHERE author = 'Author 500';
-- type: ref (Using index!)
-- rows: ~10 (Only checking relevant rows!)
-- key: idx_author

-- This is still slow!
SELECT * FROM books 
WHERE genre = 'Mystery' AND publication_year > 2020;
-- 😱 Back to 3+ seconds!

-- Create a composite index
CREATE INDEX idx_genre_year ON books(genre, publication_year);

-- Now this flies!
SELECT * FROM books 
WHERE genre = 'Mystery' AND publication_year > 2020;
-- ⚡ 0.02 seconds!

-- Order matters! This won't use our index efficiently:
SELECT * FROM books WHERE publication_year > 2020;
-- Still slow because year is second in the index

-- Users often just need title and price
CREATE INDEX idx_author_covering 
ON books(author, title, price);

-- This query never touches the main table!
SELECT title, price FROM books WHERE author = 'Author 500';
-- ⚡ 0.01 seconds! (Even faster!)

-- Most common searches:
-- 1. By author
-- 2. By genre + year
-- 3. By title (partial match)
-- 4. By price range

-- Drop old indexes
DROP INDEX idx_author ON books;
DROP INDEX idx_genre_year ON books;
DROP INDEX idx_author_covering ON books;

-- Create optimized indexes
CREATE INDEX idx_author ON books(author);
CREATE INDEX idx_genre_year_price ON books(genre, publication_year, price);
CREATE INDEX idx_title ON books(title);

-- For partial title matches, add FULLTEXT
ALTER TABLE books ADD FULLTEXT(title);

-- Test all common queries
SELECT * FROM books WHERE author = 'Author 500';  -- ⚡ 0.05s
SELECT * FROM books WHERE genre = 'Mystery' AND publication_year > 2020;  -- ⚡ 0.02s
SELECT * FROM books WHERE MATCH(title) AGAINST('Harry Potter');  -- ⚡ 0.03s
SELECT * FROM books WHERE price BETWEEN 20 AND 50;  -- ⚡ 0.08s

-- Slow query to fix:
SELECT * FROM books 
WHERE author = 'Author 100' AND genre = 'Fiction';

-- Your solution here:
-- CREATE INDEX ...

-- Currently slow:
SELECT * FROM books 
WHERE genre = 'Science' 
ORDER BY rating DESC 
LIMIT 10;

-- Hint: Indexes can help with sorting too!

SELECT title, author, price 
FROM books 
WHERE (genre = 'Fiction' OR genre = 'Fantasy')
  AND publication_year >= 2000
  AND rating > 4.0
ORDER BY price ASC
LIMIT 20;

-- Basic index (single column)
CREATE INDEX idx_name ON table(column);

-- Composite index (multiple columns)
CREATE INDEX idx_name ON table(col1, col2, col3);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_name ON table(column);

-- Fulltext index (for text search)
ALTER TABLE table ADD FULLTEXT(column);

-- Check what indexes exist
SHOW INDEX FROM table;

-- Drop an index
DROP INDEX idx_name ON table;