質問編集履歴

2

Update文をSelectに変えたQueryのexplainを追記

2017/02/21 17:47

投稿

退会済みユーザー
test CHANGED
File without changes
test CHANGED
@@ -19,3 +19,27 @@
19
19
 
20
20
 
21
21
  UPDATE test_table SET test_date = NOW() WHERE id IN ( SELECT id FROM (SELECT id FROM test_table WHERE test_date > NOW() ) as temp )
22
+
23
+
24
+
25
+
26
+
27
+ Update文をSelectに変えたQueryのexplainを追記
28
+
29
+
30
+
31
+ mysql> explain select id from test_table WHERE id IN ( SELECT id FROM (SELECT id FROM test_table WHERE test_date > NOW() ) as temp );
32
+
33
+ +----+-------------+------------+-------+---------------+-------------+---------+---------------------------------+------+-------------------------------------+
34
+
35
+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
36
+
37
+ +----+-------------+------------+-------+---------------+-------------+---------+---------------------------------+------+-------------------------------------+
38
+
39
+ | 1 | PRIMARY | test_table | index | PRIMARY | PRIMARY | 8 | NULL | 1 | Using index |
40
+
41
+ | 1 | PRIMARY | <derived3> | ref | <auto_key0> | <auto_key0> | 8 | development_web01.test_table.id | 2 | Using index; FirstMatch(test_table) |
42
+
43
+ | 3 | DERIVED | test_table | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
44
+
45
+ +----+-------------+------------+-------+---------------+-------------+---------+---------------------------------+------+-------------------------------------+

1

タイトルを加筆しました

2017/02/21 17:47

投稿

退会済みユーザー
test CHANGED
@@ -1 +1 @@
1
- 行ロックなるか知りたいです
1
+ 同じテーブルのサブクエリで値をUPDATEした際に行ロックなるか知りたいです
test CHANGED
File without changes