indexについて

InnoDBとMyISAMはB-Tree型のindexが貼られる
InnoDBでは更新や削除などでindexがフラグメンテーションを起こす

index再構築

CREATE/DROP INDEX文だと、InnoDBは自動的にテーブルを再構築する
そのためALTER TABLE もしくはテーブルのDROPとCREATEで対応するべし

mysql> show tables;
+-------------------------+
| Tables_in_techwordpress |
+-------------------------+
| page_fake_url           |
| wp_commentmeta          |
| wp_comments             |
| wp_links                |
| wp_options              |
| wp_postmeta             |
| wp_posts                |
| wp_term_relationships   |
| wp_term_taxonomy        |
| wp_terms                |
| wp_usermeta             |
| wp_users                |
+-------------------------+
12 rows in set (0.00 sec)

mysql> show create table wp_options\G
*************************** 1. row ***************************
       Table: wp_options
Create Table: CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=12295 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show index from wp_options;
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_options |          0 | PRIMARY     |            1 | option_id   | A         |         173 |     NULL | NULL   |      | BTREE      |         |               |
| wp_options |          0 | option_name |            1 | option_name | A         |         173 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)