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)