/* Day 20: Query processing/optimization
Tim Pierson, Dartmouth CS61, Spring 2020 
*/

-- Assume a Products table and Vendors table exist
-- Each product is produced by a Vendor
-- Find products produced by vendors in New Hampshire
SELECT ProductCode, ProductDescription, VendorName, VendorState
FROM Products p, Vendors v
WHERE p.VendorID = v.VendorID
AND v.VendorState = 'NH';

-- set up for nyc_inspections
-- clear any old indices from prior run (uncomment drop statements)
use nyc_inspections;
-- 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

-- 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 COUNT(DISTINCT(Boro)) FROM Restaurants; -- 6 as expected!
SELECT COUNT(DISTINCT(ZipCode)) FROM Restaurants; -- 225 about what is expected, remember cardinality is an estimate
SELECT DISTINCT(zipcode) from Restaurants;

SELECT COUNT(*) FROM Restaurants; -- 26,573 rows
SELECT Boro, Count(*) FROM Restaurants
GROUP BY Boro; -- 10,649 in Manhattan


EXPLAIN SELECT * FROM Restaurants
WHERE Boro = 'Manhattan'; -- scans 13,279 rows, not all 26,573 rows

EXPLAIN SELECT * FROM Restaurants
WHERE ZipCode = '10023'; -- scans 26,573 rows because ZipCode is right most (can't use it)

EXPLAIN SELECT * FROM Restaurants
WHERE Boro = 'Manhattan' AND ZipCode = '10023'; -- scans only 201 rows (uses both indices)

-- drop idx_borozip index
DROP INDEX idx_borozip ON Restaurants;

-- check out looking for restaurant by name
EXPLAIN SELECT RestaurantName 
FROM Restaurants 
WHERE RestaurantName like 'Tim%'; -- scans all 26,520 rows

-- 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!

DROP INDEX idx_name ON Restaurants;


-- PRACTICE from slide 22
-- 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
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





