4.3.10.15 Unique row not found

create table tb_test1 (a int, primary key(a));
create table tb_test2 (a int, primary key(a));

insert into tb_test1 values(1),(2);
insert into tb_test2 values(1);

EXPLAIN
SELECT t1.a
FROM   tb_test1 t1 left join tb_test2 t2
ON     t1.a=t2.a
WHERE  t1.a=2;

+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|    1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

4.3.10.16 Unique row not found

EXPLAIN
SELECT * FROM employees ORDER BY last_name DESC;

+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using filesort |
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0.00 sec)

4.3.10.17 Using index

EXPLAIN
SELECT first_name, birth_date
FROM   employees 
WHERE  first_name between 'Babette' AND 'Gad';

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)


EXPLAIN
SELECT first_name
FROM   employees 
WHERE  first_name between 'Babette' AND 'Gad';

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

4.3.10.18 Using index for group-by

EXPLAIN
SELECT first_name, count(*) as counter
FROM   employees 
GROUP BY first_name;

+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using temporary; Using filesort |
+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.00 sec)


EXPLAIN
SELECT emp_no, MIN(from_date) , MAX(from_date)
FROM   salaries
GROUP BY emp_no;

+------+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+------+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
|    1 | SIMPLE      | salaries | index | NULL          | PRIMARY | 7       | NULL | 2838426 | Using index |
+------+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.06 sec)

EXPLAIN
SELECT first_name
FROM   employees 
WHERE  birth_date > '1994-01-01'
GROUP BY first_name;

+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------------------------------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using where; Using temporary; Using filesort |
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

EXPLAIN
SELECT emp_no
FROM   salaries
WHERE  emp_no BETWEEN 10001 AND 200000
GROUP BY emp_no;

+------+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+------+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
|    1 | SIMPLE      | salaries | range | PRIMARY       | PRIMARY | 4       | NULL | 1419213 | Using where; Using index |
+------+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
1 row in set (0.05 sec)

4.3.10.19 Using join buffer(Bloack Nested Loop), Using join buffer(Batched Key Access)

EXPLAIN
SELECT *
FROM   dept_emp b, employees a
WHERE  b.from_date > '2005-01-01' AND a.emp_no <10904;

+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                           |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------------------------------------------+
|    1 | SIMPLE      | a     | range | PRIMARY       | PRIMARY | 4       | NULL |    902 | Using where                                     |
|    1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL | 331570 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------------------------------------------+
2 rows in set (0.00 sec)

4.3.10.21 Using tempoary

EXPLAIN
SELECT *
FROM   employees
GROUP BY gender
ORDER BY MIN(emp_no);

+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using temporary; Using filesort |
+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.00 sec)

4.3.10.22 Using where

EXPLAIN
SELECT *
FROM   employees
WHERE  emp_no BETWEEN 10001 AND 10100 AND gender ='F';

+------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | employees | range | PRIMARY       | PRIMARY | 4       | NULL |  100 | Using where |
+------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)

4.3.10.24 Deleting all rows

CREATE TABLE tab_delete (fd INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO  tab_delete VALUES(1),(2),(3);

EXPLAIN
DELETE FROM tab_delete;

+------+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra             |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL |    3 | Deleting all rows |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------+
1 row in set (0.07 sec)

EXPLAIN
DELETE FROM tab_delete WHERE fd=1; 

+------+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | tab_delete | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
+------+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

4.3.10.25 FirstMatch(tbl_name)

EXPLAIN
SELECT *
FROM   departments d
WHERE  d.dept_no IN (SELECT de.dept_no FROM dept_emp de);

+------+-------------+-------+-------+---------------+-----------+---------+---------------------+------+----------------------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref                 | rows | Extra                      |
+------+-------------+-------+-------+---------------+-----------+---------+---------------------+------+----------------------------+
|    1 | PRIMARY     | d     | index | PRIMARY       | dept_name | 42      | NULL                |    9 | Using index                |
|    1 | PRIMARY     | de    | ref   | dept_no       | dept_no   | 4       | employees.d.dept_no |    1 | Using index; FirstMatch(d) |
+------+-------------+-------+-------+---------------+-----------+---------+---------------------+------+----------------------------+
2 rows in set (0.00 sec)

4.3.10.26 LooseScan(m..n)

SET optimizer_switch=DEFAULT;
SET optimizer_switch='firstmatch=off';
SET optimizer_switch='materialization=off';

EXPLAIN
SELECT *
FROM   departments
WHERE  dept_no IN (SELECT dept_no FROM dept_emp);

+------+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+---------------------------------------------+
| id   | select_type | table       | type  | possible_keys | key       | key_len | ref                           | rows | Extra                                       |
+------+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+---------------------------------------------+
|    1 | PRIMARY     | departments | index | PRIMARY       | dept_name | 42      | NULL                          |    9 | Using index                                 |
|    1 | PRIMARY     | dept_emp    | ref   | dept_no       | dept_no   | 4       | employees.departments.dept_no |    1 | Using index; Start temporary; End temporary |
+------+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+---------------------------------------------+
2 rows in set (0.00 sec)

4.3.10.29 Start tempoary, End tempoary

EXPLAIN
SELECT *
FROM   employees d
WHERE  d.emp_no IN (SELECT de.dept_no FROM dept_emp de WHERE dept_no in ('d001','d003'));

+------+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows  | Extra                                     |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------------------------------------+
|    1 | PRIMARY     | de    | range  | dept_no       | dept_no | 4       | NULL                 | 75424 | Using where; Using index; Start temporary |
|    1 | PRIMARY     | d     | eq_ref | PRIMARY       | PRIMARY | 4       | employees.de.dept_no |     1 | Using where; End temporary                |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------------------------------------+
2 rows in set (0.06 sec)

4.3.10.30 Using index condition

EXPLAIN
SELECT *
FROM   employees
WHERE  first_name LIKE 'Lee%' AND first_name LIKE '%matt';

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

4.3.10.31 Rowid-ordered scan, Key-ordered scan

EXPLAIN
SELECT *
FROM   employees
WHERE  first_name >='A' AND first_name <'B';

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

EXPLAIN
SELECT *
FROM   dept_emp d,employees e
WHERE  e.emp_no=d.emp_no AND d.dept_no IN ('d001','d002');

+------+-------------+-------+--------+-----------------+---------+---------+--------------------+--------+-------------+
| id   | select_type | table | type   | possible_keys   | key     | key_len | ref                | rows   | Extra       |
+------+-------------+-------+--------+-----------------+---------+---------+--------------------+--------+-------------+
|    1 | SIMPLE      | d     | ALL    | PRIMARY,dept_no | NULL    | NULL    | NULL               | 331570 | Using where |
|    1 | SIMPLE      | e     | eq_ref | PRIMARY         | PRIMARY | 4       | employees.d.emp_no |      1 |             |
+------+-------------+-------+--------+-----------------+---------+---------+--------------------+--------+-------------+
2 rows in set (0.00 sec)

4.3.11 EXPLAIN EXTENDED(Filtered 컬럼)

EXPLAIN EXTENDED
SELECT *
FROM   employees
WHERE  emp_no BETWEEN 10001 AND 10100 AND gender='F';

+------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
| id   | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
|    1 | SIMPLE      | employees | range | PRIMARY       | PRIMARY | 4       | NULL |  100 |   100.00 | Using where |
+------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

4.3.12 EXPLAIN EXTENDED(추가 옵티마이저 정보)

EXPLAIN EXTENDED
SELECT e.first_name,
  (SELECT COUNT(*) FROM dept_emp d, dept_manager m WHERE d.dept_no=m.dept_no) AS cnt
FROM   employees e
WHERE  e.emp_no=10001;

+------+-------------+-------+-------+---------------+---------+---------+---------------------+-------+----------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref                 | rows  | filtered | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+---------------------+-------+----------+-------------+
|    1 | PRIMARY     | e     | const | PRIMARY       | PRIMARY | 4       | const               |     1 |   100.00 |             |
|    2 | SUBQUERY    | m     | index | dept_no       | dept_no | 4       | NULL                |    24 |   100.00 | Using index |
|    2 | SUBQUERY    | d     | ref   | dept_no       | dept_no | 4       | employees.m.dept_no | 20723 |   100.00 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+---------------------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

SHOW WARNINGS;

SELECT 'George' AS 'first_name'
FROM   'employees'.'employees' 'e' WHERE 1;

MariaDB [employees]> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''employees'.'employees' 'e' WHERE 1' at line 2 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

4.3.13 EXPLAIN PARTITIONS(Partions 컬럼)

CREATE TABLE tb_partition (
  reg_date DATE DEFAULT NULL,
  id INT DEFAULT NULL,
  name VARCHAR(50) DEFAULT NULL) ENGINE=INNODB
  partition BY range(YEAR(reg_date)) (
    partition p0 VALUES less than (2008) ENGINE=INNODB,
    partition p1 VALUES less than (2009) ENGINE=INNODB,
    partition p2 VALUES less than (2010) ENGINE=INNODB,
    partition p3 VALUES less than (2011) ENGINE=INNODB
  );
  
  EXPLAIN PARTITIONS
  SELECT * FROM tb_partition
  WHERE  reg_date BETWEEN '2010-01-01' AND '2010-12-30' ;

+------+-------------+--------------+------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------------+------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | tb_partition | p3         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+------+-------------+--------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

4.4 옵티마이저 힌트

4.4.1 힌트의 사용법

SELECT * FROM employees USE_INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees /*! USE_INDEX(primary)*/ WHERE emp_no=10001;

CREATE /*!32302 TEMPOARY*/ TABLE temp_emp_stat
(hire_year INT NOT NULL, emp_count INT, PRIMARY KEY(hire_year));

CREATE TEMPOARY TABLE temp_emp_stat
(hire_year INT NOT NULL, emp_count INT, PRIMARY KEY(hire_year));

4.4.2 STRAIGHT_JOIN

EXPLAIN
SELECT /*! STRAIGHT_JOIN */
       e.first_name , e.last_name, d.dept_name
FROM   employees e, departments d, dept_emp de
WHERE  e.emp_no=de.emp_no AND d.dept_no=de.dept_no;


+------+-------------+-------+--------+-----------------+-----------+---------+----------------------------------------+--------+-------------------------------------------------+
| id   | select_type | table | type   | possible_keys   | key       | key_len | ref                                    | rows   | Extra                                           |
+------+-------------+-------+--------+-----------------+-----------+---------+----------------------------------------+--------+-------------------------------------------------+
|    1 | SIMPLE      | e     | ALL    | PRIMARY         | NULL      | NULL    | NULL                                   | 299202 |                                                 |
|    1 | SIMPLE      | d     | index  | PRIMARY         | dept_name | 42      | NULL                                   |      9 | Using index; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | de    | eq_ref | PRIMARY,dept_no | PRIMARY   | 8       | employees.e.emp_no,employees.d.dept_no |      1 | Using index                                     |
+------+-------------+-------+--------+-----------------+-----------+---------+----------------------------------------+--------+-------------------------------------------------+
3 rows in set (0.00 sec)

4.4.3 USE INDEX / FORCE INDEX / IGNORE INDEX

SELECT * FROM employees WHERE emp_no=10001;
SELECT * FROM employees FORCE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees USE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees IGNORE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees FORCE INDEX(ix_firstname) WHERE emp_no=10001;

4.4.4 SQL_CACHE/SQL_NO_CACHE

SELECT COUNT(*) FROM employees WHERE last_name = 'Facello';

SELECT SQL_NO_CACHE COUNT(*) FROM employees WHERE last_name='Facello';
SELECT /*! SQL_NO_CACHE */ COUNT(*) FROM employees WHERE last_name='Facello'

4.4.5 SQL_CALC_FOUND_ROWS

SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;

MariaDB [employees]> SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.22 sec)

MariaDB [employees]> SELECT FOUND_ROWS() AS total_record_count;
+--------------------+
| total_record_count |
+--------------------+
|             300024 |
+--------------------+
1 row in set (0.00 sec)