質問編集履歴

1

回答で教授いただいた`EXPLAIN`の実行結果を追記。

2018/10/30 06:51

投稿

hanlio
hanlio

スコア14

test CHANGED
File without changes
test CHANGED
@@ -199,3 +199,69 @@
199
199
  2 rows in set (0.00 sec)
200
200
 
201
201
  ```
202
+
203
+
204
+
205
+ ---
206
+
207
+ ##(追記)
208
+
209
+
210
+
211
+ ###先頭文字マッチの`EXPLAIN`の実行結果
212
+
213
+
214
+
215
+ ```MySQL
216
+
217
+ mysql> EXPLAIN SELECT * FROM Address WHERE phone_nbr LIKE "090%";
218
+
219
+ +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
220
+
221
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
222
+
223
+ +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
224
+
225
+ | 1 | SIMPLE | Address | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 11.11 | Using where |
226
+
227
+ +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
228
+
229
+ 1 row in set, 1 warning (0.00 sec)
230
+
231
+
232
+
233
+ mysql> EXPLAIN SELECT * FROM Address WHERE phone_nbr REGEXP "^090";
234
+
235
+ +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
236
+
237
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
238
+
239
+ +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
240
+
241
+ | 1 | SIMPLE | Address | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | Using where |
242
+
243
+ +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
244
+
245
+ 1 row in set, 1 warning (0.00 sec)
246
+
247
+ ```
248
+
249
+
250
+
251
+ ###ついでのSHOW INDEX
252
+
253
+ ```MySQL
254
+
255
+ mysql> SHOW INDEX FROM Address;
256
+
257
+ +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
258
+
259
+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
260
+
261
+ +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
262
+
263
+ | address | 0 | PRIMARY | 1 | name | A | 9 | NULL | NULL | | BTREE | | |
264
+
265
+ +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
266
+
267
+ ```