/*	SQL Script
 *
 *	The following commands were executed on the `employees` database, availabe from https://launchpad.net/test-db/
 */

USE employees;

SELECT VERSION() AS 'MySQL Version';

/* Creating an index for an index-covered query */
CREATE INDEX dept_no_from_date_idx ON dept_emp (dept_no, from_date);

/* Show `dept_emp` table structure, indexes and generic data */
SHOW TABLE STATUS LIKE "dept_emp";
DESCRIBE dept_emp;
SHOW KEYS IN dept_emp;

/* The EXPLAIN shows that the subquery doesn't use a covering-index */
EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
	/* The subquery should use a covering index, but apparently isn't */
	SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`);

/* The EXPLAIN shows that the subquery DOES use a covering-index,
		thanks to the FORCE INDEX clause */
EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
	/* The subquery uses a covering index */
	SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`);

/* 
 * With execution speed measurement, it is easy to see that both of the above queries use a covering index,
 *	despite that "Using index" isn't mentioned in the first query's EXPLAIN output.
 */
 
/* The following two queries use (and should) a covering index */
SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
	SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`);

SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
	SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`);

/* The following query doesn't (and shouldn't) use a covering index */
SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
	SELECT SQL_NO_CACHE `to_date`, emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`);