質問編集履歴

4

修正

2019/12/03 05:45

投稿

退会済みユーザー
test CHANGED
File without changes
test CHANGED
@@ -68,7 +68,7 @@
68
68
 
69
69
  +----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
70
70
 
71
- | 1 | PRIMARY | table1 | NULL | ref | column1,column2 | column1 | 4 | NULL | 3654200 | 95.52 | Using index condition; Using where |
71
+ | 1 | PRIMARY | table1 | NULL | ref | column1,column2 | column1 | 4 | NULL | 3654200 | 95.52 | Using where |
72
72
 
73
73
  | 2 | UNION | table2 | NULL | range | column3,column4 | column3 | 4 | NULL | 6985 | 50.00 | Using index condition; Using where |
74
74
 

3

修正

2019/12/03 05:45

投稿

退会済みユーザー
test CHANGED
File without changes
test CHANGED
@@ -68,7 +68,7 @@
68
68
 
69
69
  +----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
70
70
 
71
- | 1 | PRIMARY | table1 | NULL | range | column1,column2 | column1 | 4 | NULL | 3654200 | 95.52 | Using index condition; Using where |
71
+ | 1 | PRIMARY | table1 | NULL | ref | column1,column2 | column1 | 4 | NULL | 3654200 | 95.52 | Using index condition; Using where |
72
72
 
73
73
  | 2 | UNION | table2 | NULL | range | column3,column4 | column3 | 4 | NULL | 6985 | 50.00 | Using index condition; Using where |
74
74
 

2

実行計画の追加

2019/12/03 05:44

投稿

退会済みユーザー
test CHANGED
File without changes
test CHANGED
@@ -9,8 +9,6 @@
9
9
  が、ORDER BYが遅いのでなんとかして早いクエリを作りたいです。
10
10
 
11
11
  また、実行計画を確認したところインデックスは使われています。
12
-
13
-
14
12
 
15
13
 
16
14
 
@@ -57,3 +55,25 @@
57
55
  table1がかなりレコード数の多いテーブルなので、UNION ALL してしまうとORDER BYが遅いです。
58
56
 
59
57
  上記クエリと同じ結果が得られるクSQLで早くする方法はないでしょうか。
58
+
59
+
60
+
61
+ ## 実行計画
62
+
63
+ ```
64
+
65
+ +----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
66
+
67
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
68
+
69
+ +----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
70
+
71
+ | 1 | PRIMARY | table1 | NULL | range | column1,column2 | column1 | 4 | NULL | 3654200 | 95.52 | Using index condition; Using where |
72
+
73
+ | 2 | UNION | table2 | NULL | range | column3,column4 | column3 | 4 | NULL | 6985 | 50.00 | Using index condition; Using where |
74
+
75
+ | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort |
76
+
77
+ +----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
78
+
79
+ ```

1

2つ目のクエリのテーブル名が間違っていました。すみません。

2019/12/03 05:42

投稿

退会済みユーザー
test CHANGED
File without changes
test CHANGED
@@ -26,7 +26,7 @@
26
26
 
27
27
  UNION ALL
28
28
 
29
- (SELECT column3 AS A, column4 AS B FROM table1 WHERE colmun3 = 101)
29
+ (SELECT column3 AS A, column4 AS B FROM table2 WHERE colmun3 = 101)
30
30
 
31
31
  ORDER BY B DESC
32
32
 
@@ -44,7 +44,7 @@
44
44
 
45
45
  UNION ALL
46
46
 
47
- (SELECT column3 AS A, column4 AS B FROM table1 WHERE colmun3 = 101)
47
+ (SELECT column3 AS A, column4 AS B FROM table2 WHERE colmun3 = 101)
48
48
 
49
49
  ORDER BY B DESC
50
50