SQL_CALC_FOUND_ROWS index LIMIT

秒数に注目。
正しいindexの使い方を分かってないのもあるけど・・・。

SQL_CALC_FOUND_ROWS のせいでlike 総当たりしてるっぽい。
indexで参照できる範囲なら問題なさそうだけども。 range → ALLに。

■■■■■■■■■■■■■■■■■
mysql> SELECT  I.Item_id FROM (ItemTbl I) WHERE   I.Item_code  LIKE '62%'ORDER BY I.Item_name asc LIMIT 3;
+---------+
| Item_id |
+---------+
|    1135 |
|    1154 |
|    1155 |
+---------+
3 rows in set (8.66 sec)
+----+-------------+-------+-------+-----------------------+-----------------------+---------+------+--------+-----------------------------+
| id | select_type | table | type  | possible_keys         | key                   | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+-------+-----------------------+-----------------------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | I     | range | Item_code_name_sid_id | Item_code_name_sid_id | 15      | NULL | 472334 | Using where; Using filesort |
+----+-------------+-------+-------+-----------------------+-----------------------+---------+------+--------+----------------


■■■■■■■■■■■■■■■■■
mysql> SELECT SQL_CALC_FOUND_ROWS I.Item_id FROM (ItemTbl I) WHERE   I.Item_code  LIKE '62%'ORDER BY I.Item_name asc LIMIT 3;
+---------+
| Item_id |
+---------+
|    1135 |
|    1154 |
|    1155 |
+---------+
3 rows in set (38.11 sec)
mysql> explain SELECT SQL_CALC_FOUND_ROWS I.Item_id FROM (ItemTbl I) WHERE   I.Item_code  LIKE '62%'ORDER BY I.Item_name asc LIMIT 3;
+----+-------------+-------+------+-----------------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys         | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+------+-----------------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | I     | ALL  | Item_code_name_sid_id | NULL | NULL    | NULL | 637347 | Using where; Using filesort |
+----+-------------+-------+------+-----------------------+------+---------+------+--------+-----------------------------+

limitをつけると途中までINDEXを使用して無駄コストが掛かっている・・？ ALL→range

■■■■■■■■■■■■■■■■■
mysql> SELECT count( I.Item_id ) FROM (ItemTbl I) WHERE   I.Item_code  LIKE '62%'ORDER BY I.Item_name asc;
+--------------------+
| count( I.Item_id ) |
+--------------------+
|             595831 |
+--------------------+
1 row in set (0.44 sec)
+----+-------------+-------+------+-----------------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys         | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+-----------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | I     | ALL  | Item_code_name_sid_id | NULL | NULL    | NULL | 637347 | Using where |
+----+-------------+-------+------+-----------------------+------+---------+------+--------+-------------+


■■■■■■■■■■■■■■■■■
mysql> SELECT count( I.Item_id ) FROM (ItemTbl I) WHERE   I.Item_code  LIKE '62%'ORDER BY I.Item_name asc LIMIT 3;
+--------------------+
| count( I.Item_id ) |
+--------------------+
|             595831 |
+--------------------+
1 row in set (4.92 sec)
+----+-------------+-------+-------+-----------------------+-----------------------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys         | key                   | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+-----------------------+-----------------------+---------+------+--------+-------------+
|  1 | SIMPLE      | I     | range | Item_code_name_sid_id | Item_code_name_sid_id | 15      | NULL | 472334 | Using where |
+----+-------------+-------+-------+-----------------------+-----------------------+---------+------+--------+-------------+