USE sakila;

-- =====================================================
-- HANDS-ON 1: Analyze Query Performance
-- =====================================================

-- Step 1: Check current indexes
SHOW INDEXES FROM film;

-- Step 2: Analyze query WITHOUT index on title
EXPLAIN SELECT * FROM film WHERE title = 'ACADEMY DINOSAUR';
-- Note: type=ALL, rows=1000 (full table scan!)

-- Step 3: Create index
CREATE INDEX idx_film_title ON film(title);

-- Step 4: Analyze query WITH index
EXPLAIN SELECT * FROM film WHERE title = 'ACADEMY DINOSAUR';
-- Note: type=ref, rows=1 (index lookup!)

-- Step 5: Compare execution time
SET profiling = 1;

-- Without index (drop it first to test)
DROP INDEX idx_film_title ON film;
SELECT * FROM film WHERE title = 'ACADEMY DINOSAUR';

-- With index
CREATE INDEX idx_film_title ON film(title);
SELECT * FROM film WHERE title = 'ACADEMY DINOSAUR';

SHOW PROFILES;

-- =====================================================
-- HANDS-ON 2: Create Indexes for Common Queries
-- =====================================================

-- Query 1: Single column index
CREATE INDEX idx_customer_lastname ON customer(last_name);

-- Query 2: Single column index
CREATE INDEX idx_film_rental_duration ON film(rental_duration);

-- Query 3: Composite index (most selective column first)
CREATE INDEX idx_film_year_rating ON film(release_year, rating);
-- Or prioritize rating if it's more selective
CREATE INDEX idx_film_rating_year ON film(rating, release_year);

-- Query 4: Index on foreign key (if not exists)
SHOW INDEXES FROM inventory;
-- inventory.film_id should already have index from foreign key
-- But title needs index on film table
CREATE INDEX idx_film_title ON film(title);

-- Verify indexes were created
SHOW INDEXES FROM customer;
SHOW INDEXES FROM film;
SHOW INDEXES FROM inventory;

-- Test performance improvement
EXPLAIN SELECT * FROM customer WHERE last_name = 'SMITH';
EXPLAIN SELECT * FROM film WHERE rental_duration = 5;
EXPLAIN SELECT * FROM film WHERE release_year = 2006 AND rating = 'PG-13';

-- =====================================================
-- HANDS-ON 3: Composite Index Order
-- =====================================================

-- Create composite index
CREATE INDEX idx_rating_duration ON film(rating, rental_duration);

-- Test Query 1: Uses index (leftmost column)
EXPLAIN SELECT * FROM film WHERE rating = 'PG';
-- Result: type=ref, key=idx_rating_duration ✅

-- Test Query 2: CANNOT use this index (not leftmost column)
EXPLAIN SELECT * FROM film WHERE rental_duration = 5;
-- Result: type=ALL (full table scan) ❌

-- Test Query 3: Uses index fully
EXPLAIN SELECT * FROM film WHERE rating = 'PG' AND rental_duration = 5;
-- Result: type=ref, key=idx_rating_duration ✅

-- Key lesson: Composite index works LEFT to RIGHT
-- Format: (col1, col2, col3)
-- Works for: col1, col1+col2, col1+col2+col3
-- NOT work for: col2, col3, col2+col3

-- If you need both queries, create TWO indexes:
CREATE INDEX idx_rating_duration ON film(rating, rental_duration);
CREATE INDEX idx_duration ON film(rental_duration);

-- PARTITION

CREATE TABLE rental_partitioned SELECT * FROM rental;

SHOW INDEXES FROM rental_partitioned WHERE Key_name = 'PRIMARY';

ALTER TABLE rental_partitioned ADD PRIMARY KEY (rental_id, rental_date);

ALTER TABLE rental_partitioned
PARTITION BY RANGE (YEAR(rental_date)) (
    PARTITION p2005 VALUES LESS THAN (2006),
    PARTITION p2006 VALUES LESS THAN (2007),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

SELECT 
    PARTITION_NAME,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION,
    TABLE_ROWS,
    CREATE_TIME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'sakila' 
  AND TABLE_NAME = 'rental'
ORDER BY PARTITION_ORDINAL_POSITION;

EXPLAIN
SELECT * FROM rental_partitioned 
WHERE rental_date >= '2005-01-01' 
  AND rental_date < '2006-01-01';
-- Should show: partitions: p2005