innodbでtextをvacharに変えただけで爆遅のクエリがなおった



##条件
###対象クエリ
```sql

EXPLAIN
SELECT * from cv_log c WHERE c.op_reserve_no IN (SELECT c2.op_reserve_no FROM cv_log c2 GROUP BY c2.op_reserve_no HAVING count(*) > 1) ORDER BY c.cv_time DESC;
```
###カウント
```sql

mysql> SELECT count(*) from cv_log;
+----------+
| count(*) |
+----------+
|     4655 |
+----------+
```

###テーブル定義
```sql

mysql> desc test.cv_log;
+--------------------------------+-------------+------+-----+---------+----------------+
| Field                          | Type        | Null | Key | Default | Extra          |
+--------------------------------+-------------+------+-----+---------+----------------+
| id                             | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| create_time                    | datetime(6) | NO   |     | NULL    |                |
|  ========================================　いっぱい!!略 ============================   |         
| op_reserve_no                  | text        | YES  |     | NULL    |                |
+--------------------------------+-------------+------+-----+---------+----------------+
74 rows in set (0.01 sec)
```

##クエリ投げてみる
3分47秒かかるw

```sql

~ % time (cat sql.txt| mysql -u root test > /dev/null)
( cat sql.txt | mysql -u root test > /dev/null; )  0.01s user 0.00s system 0% cpu 3:47.48 total
```

DEPENDENT SUBQUERYになっているのがいけない？

```sql

mysql> EXPLAIN SELECT * from cv_log c WHERE c.op_reserve_no IN (SELECT c2.op_reserve_no FROM cv_log c2 GROUP BY c2.op_reserve_no HAVING count(*) > 1) ORDER BY c.cv_time DESC;
+----+--------------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+--------------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY            | c     | ALL  | NULL          | NULL | NULL    | NULL | 4365 | Using where; Using filesort     |
|  2 | DEPENDENT SUBQUERY | c2    | ALL  | NULL          | NULL | NULL    | NULL | 4365 | Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+------+---------+------+------+---------------------------------+
```

##とりあえず、text -> varcharに変更してみる

```sql
mysql> ALTER TABLE cv_log MODIFY op_reserve_no VARCHAR(255);
Query OK, 4655 rows affected (0.19 sec)
Records: 4655  Duplicates: 0  Warnings: 0
```

##クエリ投げてみる
0.04秒wはええw
型変えただけなのになぜですか。

```sql

~ % time (cat sql.txt| mysql -u root test > /dev/null)
( cat sql.txt | mysql -u root test > /dev/null; )  0.01s user 0.00s system 36% cpu 0.041 total
```

SUBQUERYになってる。なぜ？（※今度調べる）

```sql

mysql> EXPLAIN SELECT * from cv_log c WHERE c.op_reserve_no IN (SELECT c2.op_reserve_no FROM cv_log c2 GROUP BY c2.op_reserve_no HAVING count(*) > 1) ORDER BY c.cv_time DESC;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | c     | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort     |
|  2 | SUBQUERY    | c2    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

```

##ついでにINDEX貼ったらちょっと早くなった
```sql

mysql> ALTER TABLE cv_log ADD INDEX index_posts_on_op_reserve_no(op_reserve_no)
    -> ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * from cv_log c WHERE c.op_reserve_no IN (SELECT c2.op_reserve_no FROM cv_log c2 GROUP BY c2.op_reserve_no HAVING count(*) > 1) ORDER BY c.cv_time DESC;
+----+-------------+-------+-------+------------------------------+------------------------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys                | key                          | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+------------------------------+------------------------------+---------+------+------+-----------------------------+
|  1 | PRIMARY     | c     | ALL   | NULL                         | NULL                         | NULL    | NULL | 4335 | Using where; Using filesort |
|  2 | SUBQUERY    | c2    | index | index_posts_on_op_reserve_no | index_posts_on_op_reserve_no | 768     | NULL | 4335 | Using index                 |
+----+-------------+-------+-------+------------------------------+------------------------------+---------+------+------+-----------------------------+
2 rows in set (0.00 sec)

~ % time (cat sql.txt| mysql -u root test > /dev/null)
( cat sql.txt | mysql -u root test > /dev/null; )  0.01s user 0.01s system 67% cpu 0.029 total

```