質問編集履歴
1
回答で教授いただいた`EXPLAIN`の実行結果を追記。
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
|
+
```
|