質問編集履歴
4
修正
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -33,7 +33,7 @@
|
|
|
33
33
|
+----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
|
|
34
34
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
35
35
|
+----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
|
|
36
|
-
| 1 | PRIMARY | table1 | NULL | ref | column1,column2 | column1 | 4 | NULL | 3654200 | 95.52 | Using
|
|
36
|
+
| 1 | PRIMARY | table1 | NULL | ref | column1,column2 | column1 | 4 | NULL | 3654200 | 95.52 | Using where |
|
|
37
37
|
| 2 | UNION | table2 | NULL | range | column3,column4 | column3 | 4 | NULL | 6985 | 50.00 | Using index condition; Using where |
|
|
38
38
|
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort |
|
|
39
39
|
+----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
|
3
修正
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -33,7 +33,7 @@
|
|
|
33
33
|
+----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
|
|
34
34
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
35
35
|
+----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
|
|
36
|
-
| 1 | PRIMARY | table1 | NULL |
|
|
36
|
+
| 1 | PRIMARY | table1 | NULL | ref | column1,column2 | column1 | 4 | NULL | 3654200 | 95.52 | Using index condition; Using where |
|
|
37
37
|
| 2 | UNION | table2 | NULL | range | column3,column4 | column3 | 4 | NULL | 6985 | 50.00 | Using index condition; Using where |
|
|
38
38
|
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort |
|
|
39
39
|
+----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
|
2
実行計画の追加
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -5,7 +5,6 @@
|
|
|
5
5
|
が、ORDER BYが遅いのでなんとかして早いクエリを作りたいです。
|
|
6
6
|
また、実行計画を確認したところインデックスは使われています。
|
|
7
7
|
|
|
8
|
-
|
|
9
8
|
クエリは以下の通りです、
|
|
10
9
|
|
|
11
10
|
1ページ目
|
|
@@ -27,4 +26,15 @@
|
|
|
27
26
|
```
|
|
28
27
|
|
|
29
28
|
table1がかなりレコード数の多いテーブルなので、UNION ALL してしまうとORDER BYが遅いです。
|
|
30
|
-
上記クエリと同じ結果が得られるクSQLで早くする方法はないでしょうか。
|
|
29
|
+
上記クエリと同じ結果が得られるクSQLで早くする方法はないでしょうか。
|
|
30
|
+
|
|
31
|
+
## 実行計画
|
|
32
|
+
```
|
|
33
|
+
+----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
|
|
34
|
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
35
|
+
+----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
|
|
36
|
+
| 1 | PRIMARY | table1 | NULL | range | column1,column2 | column1 | 4 | NULL | 3654200 | 95.52 | Using index condition; Using where |
|
|
37
|
+
| 2 | UNION | table2 | NULL | range | column3,column4 | column3 | 4 | NULL | 6985 | 50.00 | Using index condition; Using where |
|
|
38
|
+
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort |
|
|
39
|
+
+----+--------------+---------------+------------+-------+--------------------+---------+---------+------+---------+----------+------------------------------------+
|
|
40
|
+
```
|
1
2つ目のクエリのテーブル名が間違っていました。すみません。
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -12,7 +12,7 @@
|
|
|
12
12
|
```SQL
|
|
13
13
|
(SELECT column1 AS A, column2 AS B FROM table1 WHERE colmun1 = 100)
|
|
14
14
|
UNION ALL
|
|
15
|
-
(SELECT column3 AS A, column4 AS B FROM
|
|
15
|
+
(SELECT column3 AS A, column4 AS B FROM table2 WHERE colmun3 = 101)
|
|
16
16
|
ORDER BY B DESC
|
|
17
17
|
LIMIT 1, 20;
|
|
18
18
|
```
|
|
@@ -21,7 +21,7 @@
|
|
|
21
21
|
```SQL
|
|
22
22
|
(SELECT column1 AS A, column2 AS B FROM table1 WHERE colmun1 = 100)
|
|
23
23
|
UNION ALL
|
|
24
|
-
(SELECT column3 AS A, column4 AS B FROM
|
|
24
|
+
(SELECT column3 AS A, column4 AS B FROM table2 WHERE colmun3 = 101)
|
|
25
25
|
ORDER BY B DESC
|
|
26
26
|
LIMIT 21, 20;
|
|
27
27
|
```
|