Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE employees;
- /* 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 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 use 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`);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement