Tag Archives: Force Index

「USE INDEX 」と「FORCE INDEX 」の違い
USE INDEXでは、possible_keysにインデックスがリストアップされない場合に、
リストに加えるために利用します。ただ、それが実際に使われる(keyに表示される)かどうかは
オプティマイザの判断にゆだねられます。
それに対して、FORCE INDEXは実際に使われる(keyに表示される)ように強制するものです。

以下ケースでは、インデックスに対応するデータがある場合はヒント通りに実行されている。

root@localhost [sakila]>select * from actor limit 0,10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)

root@localhost [sakila]>show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [sakila]>ALTER TABLE actor ADD INDEX idx_fl(first_name,last_name);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [sakila]>ALTER TABLE actor ADD index idx_actor_first_name (first_name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [sakila]>show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`),
  KEY `idx_fl` (`first_name`,`last_name`),
  KEY `idx_actor_first_name` (`first_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [sakila]>



root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY';
+----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys               | key    | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_fl,idx_actor_first_name | idx_fl | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>explain select * from actor use index(idx_actor_first_name) where first_name = 'JOHNNY';
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys        | key                  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_actor_first_name | idx_actor_first_name | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>explain select * from actor force index(idx_actor_first_name) where first_name = 'JOHNNY';
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys        | key                  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_actor_first_name | idx_actor_first_name | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>


root@localhost [sakila]>explain select * from actor use index(idx_actor_last_name) where first_name = 'JOHNNY';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

root@localhost [sakila]>explain select * from actor force index(idx_actor_last_name) where first_name = 'JOHNNY';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

root@localhost [sakila]>

root@localhost [sakila]>select * from actor where last_name = 'CAGE';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       11 | ZERO       | CAGE      | 2006-02-15 04:34:33 |
|       40 | JOHNNY     | CAGE      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

root@localhost [sakila]>explain select * from actor where last_name = 'CAGE';
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_actor_last_name | idx_actor_last_name | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>explain select * from actor use index(idx_fl) where first_name = 'JOHNNY';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_fl        | idx_fl | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>explain select * from actor force index(idx_fl) where first_name = 'JOHNNY';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_fl        | idx_fl | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>

その他、パフォーマンス関連の確認に関してはSYSスキーマを利用すると良いでしょう。

GITからのダウンロード。
英語ですが、こちらのページに各Viewの詳細が説明されております。
https://github.com/MarkLeith/mysql-sys

SYS Schemaに関しては、こちらが参考になるかと思います。
https://oracleus.activeevents.com/2014/connect/fileDownload/session/72527FD42DFF7B2148314B9E72BE7B6A/CON3751_Leith-mysql_sys_schema_oow_2014.pdf