EXPLAIN
SELECT *
FROM employees
WHERE first_name = 'Matt' -- 233 건
AND hire_date BETWEEN '1995-01-01' AND '2000-01-01' -- 34028 건
;
+------+-------------+-----------+------+--------------------------+--------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+--------------------------+--------------+---------+-------+------+------------------------------------+
| 1 | SIMPLE | employees | ref | ix_firstname,ix_hiredate | ix_firstname | 44 | const | 233 | Using index condition; Using where |
+------+-------------+-----------+------+--------------------------+--------------+---------+-------+------+------------------------------------+
EXPLAIN
SELECT *
FROM employees
WHERE first_name BETWEEN 'A' AND 'B' -- 22039 건
AND hire_date BETWEEN '1995-01-01' AND '2000-01-01' -- 34028 건
;
+------+-------------+-----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+
| 1 | SIMPLE | employees | range | ix_firstname,ix_hiredate | ix_firstname | 44 | NULL | 43234 | Using index condition; Using where |
+------+-------------+-----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+
MariaDB [employees]> SET optimizer_switch = 'index_merge_sort_intersection = on'; -- 공백 때문에 에러 발생
ERROR 1231 (42000): Variable 'optimizer_switch' can't be set to the value of 'index_merge_sort_intersection = on'
MariaDB [employees]> SET optimizer_switch = 'index_merge_sort_intersection=on'; -- 공백 지우고 정상 수행
Query OK, 0 rows affected (0.00 sec)
EXPLAIN
SELECT *
FROM employees
WHERE first_name BETWEEN 'A' AND 'B' -- 22039 건
AND hire_date BETWEEN '1995-01-01' AND '2000-01-01' -- 34028 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
| 1 | SIMPLE | employees | index_merge | ix_firstname,ix_hiredate | ix_firstname,ix_hiredate | 44,3 | NULL | 9912 | Using sort_intersect(ix_firstname,ix_hiredate); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
EXPLAIN
SELECT *
FROM employees
WHERE first_name = 'Matt' -- 233 건
OR hire_date = '1987-03-31' -- 111 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | employees | index_merge | ix_firstname,ix_hiredate | ix_firstname,ix_hiredate | 44,3 | NULL | 344 | Using union(ix_firstname,ix_hiredate); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+----------------------------------------------------+
EXPLAIN
SELECT *
FROM employees
WHERE first_name LIKE '%Matt' -- 233 건
OR hire_date = '1987-03-31' -- 111 건
;
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | employees | ALL | ix_hiredate | NULL | NULL | NULL | 299423 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
SELECT *
FROM employees
WHERE first_name = 'Matt' -- 233 건
OR hire_date = '1987-03-31' -- 111 건
LIMIT 10
;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10083 | 1959-07-23 | Vishv | Zockler | M | 1987-03-31 |
| 10690 | 1962-09-06 | Matt | Jumpertz | F | 1989-08-22 |
| 12302 | 1962-02-14 | Matt | Plessier | M | 1987-01-28 |
| 13163 | 1963-12-11 | Matt | Heping | F | 1987-03-31 |
| 13233 | 1954-08-11 | Alselm | Chvatal | M | 1987-03-31 |
| 13507 | 1959-09-05 | Matt | Wallrath | M | 1985-06-28 |
| 14004 | 1959-04-10 | Arto | Wendorf | M | 1987-03-31 |
| 15456 | 1957-09-27 | Matt | Granlund | M | 1987-08-17 |
| 15577 | 1961-02-15 | Matt | Menhardt | M | 1985-10-26 |
| 15696 | 1959-05-06 | Matt | Zhang | F | 1987-08-21 |
+--------+------------+------------+-----------+--------+------------+
EXPLAIN
SELECT *
FROM employees
WHERE first_name = 'Matt' -- 233 건
OR hire_date BETWEEN '1987-03-01' AND '1987-03-31' -- 2964 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------+
| 1 | SIMPLE | employees | index_merge | ix_firstname,ix_hiredate | ix_firstname,ix_hiredate | 44,3 | NULL | 3196 | Using sort_union(ix_firstname,ix_hiredate); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------+
EXPLAIN
SELECT COUNT(*)
FROM employees
WHERE first_name = 'Matt' -- 233 건
AND hire_date = '1991-07-22' -- 47 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------------------+
| 1 | SIMPLE | employees | index_merge | ix_firstname,ix_hiredate | ix_hiredate,ix_firstname | 3,44 | NULL | 1 | Using intersect(ix_hiredate,ix_firstname); Using where; Using index |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------------------+
EXPLAIN
SELECT COUNT(*)
FROM employees
WHERE first_name LIKE 'Matt%' -- 233 건
AND hire_date = '1991-07-22' -- 47 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
| 1 | SIMPLE | employees | index_merge | ix_firstname,ix_hiredate | ix_hiredate,ix_firstname | 3,44 | NULL | 1 | Using sort_intersect(ix_hiredate,ix_firstname); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
- 강좌 URL : http://www.gurubee.net/lecture/4197
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.