質問編集履歴
4
修正
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
|
|
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
修正
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -68,7 +68,7 @@
|
|
|
68
68
|
|
|
69
69
|
+----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
|
|
70
70
|
|
|
71
|
-
| 1 | PRIMARY | table1 | NULL | r
|
|
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
実行計画の追加
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つ目のクエリのテーブル名が間違っていました。すみません。
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 table
|
|
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 table
|
|
47
|
+
(SELECT column3 AS A, column4 AS B FROM table2 WHERE colmun3 = 101)
|
|
48
48
|
|
|
49
49
|
ORDER BY B DESC
|
|
50
50
|
|