/* Day 13: Query optimization
Tim Pierson, Dartmouth CS61, Spring 2026
*/

-- SLIDE 3
-- Normalized databases mean more tables, which means more complex queries
USE nyc_data;
SELECT r.RestaurantID, RestaurantName, InspectionDate, ViolationCodeDescription
	FROM Restaurants r JOIN Inspections i ON r.RestaurantID = i.RestaurantID
		JOIN InspectionViolations iv ON iv.InspectionID = i.InspectionID
        JOIN ViolationCodes vc ON vc.ViolationCode = iv.ViolationCode
	WHERE RestaurantName LIKE 'Morris Park Bake%'
    ORDER BY InspectionDate;

SELECT * FROM restaurants WHERE RestaurantName LIKE 'Morris Park%' ; -- 3 matches

-- SLIDE 33
ANALYZE TABLE Restaurants, Inspections, InspectionViolations, ViolationCodes;

-- get table counts
SELECT COUNT(*) FROM Restaurants; -- 30,481
SELECT COUNT(*) FROM Inspections; -- 52,890
SELECT COUNT(*) FROM InspectionViolations; -- 133,186
SELECT COUNT(*) FROM ViolationCodes; -- 151

SELECT database_name, table_name, n_rows, clustered_index_size
	FROM mysql.innodb_table_stats
	WHERE database_name = DATABASE();
 
-- SLIDE 34 
-- see costs, they are stored in two tables
-- mysql.engine_cost
SELECT * 
	FROM mysql.engine_cost;
    
-- mysql.server_cost
SELECT * 
	FROM mysql.server_cost;
  
-- combine costs from both tables
SELECT cost_name, default_value
	FROM mysql.engine_cost
UNION
SELECT cost_name, default_value
	FROM mysql.server_cost;
                     





-- SLIDE 36
-- clear any old indices from prior run (uncomment drop statements)
-- DROP INDEX idx_borozip ON Restaurants;
-- DROP INDEX idx_name ON Restaurants;

-- we can see existing indicies with SHOW INDEXES
SHOW INDEXES FROM Restaurants;
-- have index for PK on RestaurantID and also for FK on CuisineID

-- SLIDE 37
-- index doesn't help if the query is not on the index
SELECT *
	FROM Restaurants
	WHERE Boro = 'Manhattan';

-- SLIDE 38
EXPLAIN SELECT *
	FROM Restaurants
	WHERE Boro = 'Manhattan';
    
SELECT COUNT(*)
	FROM Restaurants
	WHERE Boro = 'Manhattan'; -- 12,080
    

-- SLIDE 46
-- indices can be created on multiple columns
-- called a composite index
CREATE INDEX idx_borozip ON Restaurants(Boro,ZipCode);
SHOW INDEXES FROM Restaurants;
-- optimizer can use index left most attributes
-- here can use on Boro or Boro and ZipCode
-- but not on just ZipCode (left most not used)
-- cardinality estimate on boro is only 6 (e.g., there are six different boros in the data)
-- cardinality estimate on zip is 233 (e.g., there are 233 different zip codes)
-- let's check cardinality
SELECT DISTINCT(Boro) FROM Restaurants; 
SELECT COUNT(*) FROM Restaurants WHERE boro = '0'; -- 16 have no boro or address, delete them?
SELECT COUNT(DISTINCT(Boro)) FROM Restaurants; -- 6, but 5 were expected!  a few rows have 0 as boro
SELECT COUNT(DISTINCT(ZipCode)) FROM Restaurants; -- 224 about what is expected, remember cardinality is an estimate
SELECT DISTINCT(zipcode) from Restaurants;

SELECT COUNT(*) FROM Restaurants; -- 30,481 rows
SELECT Boro, Count(*) FROM Restaurants
	GROUP BY Boro; -- 12,080 in Manhattan
    
-- SLIDE 47
-- try query now with index on boro and zip
ANALYZE TABLE Restaurants;

SELECT *
	FROM Restaurants
	WHERE Boro = 'Manhattan';
    
EXPLAIN SELECT *
	FROM Restaurants
	WHERE Boro = 'Manhattan';

  
  -- SLIDE 50
  EXPLAIN SELECT *
	FROM Restaurants
    WHERE ZipCode = 10023;
  
  EXPLAIN SELECT *
	FROM Restaurants
    WHERE ZipCode = 10023 and Boro = 'Manhattan';
    
    SELECT COUNT(*) 
		FROM Restaurants
        WHERE ZipCode = 10023 and Boro = 'Manhattan';

-- SLIDE 50
-- drop idx_borozip index
DROP INDEX idx_borozip ON Restaurants;

-- looking for restaurant by name (no index on name)
EXPLAIN SELECT RestaurantName 
	FROM Restaurants 
	WHERE RestaurantName like 'Tim%'; -- scans all rows

-- SLIDE 51
-- create a new one based on first 3 characters of restaurant name
CREATE INDEX idx_name ON Restaurants(RestaurantName(3));
SHOW INDEXES FROM Restaurants; -- cardinality is 3032

-- can hint (or force) query to using index
EXPLAIN SELECT RestaurantName 
FROM Restaurants USE INDEX(idx_name) -- can replace USE with FORCE
WHERE RestaurantName like 'Tim%'; -- scans only 18 rows!


-- SLIDE 52
DROP INDEX idx_name ON Restaurants;
EXPLAIN SELECT r.RestaurantID, RestaurantName, InspectionDate, ViolationCodeDescription
FROM Restaurants r
JOIN Inspections i           ON r.RestaurantID = i.RestaurantID
JOIN InspectionViolations iv ON iv.InspectionID = i.InspectionID
JOIN ViolationCodes vc       ON vc.ViolationCode = iv.ViolationCode
WHERE RestaurantName LIKE 'Morris Park Bake%'
ORDER BY InspectionDate;

/*
-> Sort: i.InspectionDate
    -> Stream results  (cost=15787 rows=18511)
        -> Nested loop inner join  (cost=15787 rows=18511)
            -> Nested loop inner join  (cost=9309 rows=18511)
                -> Nested loop inner join  (cost=5633 rows=7231)
                    -> Filter: (r.RestaurantName like 'Morris Park Bake%')  (cost=3102 rows=3366)
                        -> Table scan on r  (cost=3102 rows=30296)
                    -> Index lookup on i using fk_Inspections_Restaurants_idx (RestaurantID = r.RestaurantID)  (cost=0.537 rows=2.15)
                -> Covering index lookup on iv using fk_InspectionViolations_Inspections1_idx (InspectionID = i.InspectionID)  (cost=0.252 rows=2.56)
            -> Single-row index lookup on vc using PRIMARY (ViolationCode = iv.ViolationCode)  (cost=0.25 rows=1)
*/


-- SLIDE 62
-- There were a lot of errors in the estimate because MySQL doesn't know what to 
-- expect from the RestauarntName LIKE 'Morris Park Bake%'
-- It guessed there would be 3366 rows, but there was only 1!
-- add an index on RestaurantName and see what happens
CREATE INDEX idx_name ON Restaurants(RestaurantName);
SHOW INDEXES FROM Restaurants; -- have index based first 3 characters

EXPLAIN SELECT r.RestaurantID, RestaurantName, InspectionDate, ViolationCodeDescription
FROM Restaurants r
JOIN Inspections i           ON r.RestaurantID = i.RestaurantID
JOIN InspectionViolations iv ON iv.InspectionID = i.InspectionID
JOIN ViolationCodes vc       ON vc.ViolationCode = iv.ViolationCode
WHERE RestaurantName LIKE 'Morris Park Bake%'
ORDER BY InspectionDate;




-- PRACTICE from slide 68
-- run customers_schema.sql first to create Customers schema and table

use Customers;
-- look around
SELECT * FROM Customers;
SELECT COUNT(*) FROM Customers;
SHOW INDEXES FROM Customers;

/*
-- create some indexes on Customers table
-- (CREATE INDEX commands now part of customers_schema.sql)
CREATE INDEX idx_LastName on Customers(ContactLastName);
CREATE INDEX idx_FirstName on Customers(ContactFirstName);
CREATE INDEX idx_FirstLastName on Customers(ContactFirstName,ContactLastName);
CREATE INDEX idx_LastFirstName on Customers(ContactLastName,ContactFirstName);
*/
SHOW INDEXES FROM Customers;

-- see what MySQL wants to use for indices
EXPLAIN SELECT * FROM Customers
WHERE ContactFirstName like 'A%'
OR ContactLastName LIKE 'A%';
-- idx_FirstName and idx_LastName
-- seems smart

-- run it and see how long it takes to execute
-- Can also use EXPLAIN ANALYZE
SELECT * FROM Customers
WHERE ContactFirstName like 'A%'
OR ContactLastName LIKE 'A%'; 
-- 0.00078 seconds

-- try forcing the composite indices
EXPLAIN SELECT * FROM Customers
USE INDEX (idx_FirstLastName, idx_LastFirstName)
WHERE ContactFirstName like 'A%'
OR ContactLastName LIKE 'A%';

-- see if it helps
SELECT * FROM Customers
USE INDEX (idx_FirstLastName, idx_LastFirstName)
WHERE ContactFirstName like 'A%'
OR ContactLastName LIKE 'A%';
-- 0.00056 secs

