teratail
回答率
85.48%
質問するログイン新規登録
トップMySQLに関する質問【MySQL】約１５万件のテーブルになってからクエリが遅くなりました。テーブル設計とクエリを公開しますのでアドバイスお願いしたいです。

編集履歴

回答編集履歴

2

追加情報を加味した回答を追記

2016/04/14 08:35

投稿

KiyoshiMotoki
KiyoshiMotoki

スコア4791

test CHANGED
@@ -129,3 +129,61 @@
129
129
  ちなみに、ご提示のクエリだとペットを飼っている人が１人もいない趣味は結果に含まれないことになりますが、
130
130
 
131
131
  それは問題ないでしょうか？
132
+
133
+
134
+
135
+ ---
136
+
137
+
138
+
139
+ `FORCE INDEX`が聞いたということでしたら、以下２通りのクエリを試していただけますでしょうか？
140
+
141
+ ```sql
142
+
143
+ SELECT t_syumi.syumi_id, t_syumi.syumi_name, tmp.cnt
144
+
145
+ FROM t_syumi
146
+
147
+ INNER JOIN (
148
+
149
+ SELECT syumi_id, COUNT(DISTINCT pet_name) AS cnt
150
+
151
+ FROM t_petlist
152
+
153
+ FORCE INDEX FOR GROUP BY (pet_name)
154
+
155
+ INNER JOIN t_setting ON t_setting.user_id = t_petlist.user_id
156
+
157
+ GROUP BY syumi_id
158
+
159
+ ) AS tmp ON t_syumi.syumi_id = tmp.syumi_id
160
+
161
+ ORDER BY cnt DESC;
162
+
163
+ ```
164
+
165
+
166
+
167
+ ```sql
168
+
169
+ SELECT t_syumi.syumi_id, t_syumi.syumi_name, tmp.cnt
170
+
171
+ FROM t_syumi
172
+
173
+ INNER JOIN (
174
+
175
+ SELECT syumi_id, COUNT(DISTINCT pet_name) AS cnt
176
+
177
+ FROM t_setting
178
+
179
+ FORCE INDEX FOR GROUP BY (syumi_id)
180
+
181
+ INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id
182
+
183
+ GROUP BY syumi_id
184
+
185
+ ) AS tmp ON t_syumi.syumi_id = tmp.syumi_id
186
+
187
+ ORDER BY cnt DESC;
188
+
189
+ ```

1

回答を追記

2016/04/14 08:35

投稿

KiyoshiMotoki
KiyoshiMotoki

スコア4791

test CHANGED
@@ -5,3 +5,127 @@
5
5
 
6
6
 
7
7
  また、"情報の追加・修正の依頼をする"欄に記載させていただいた情報を追記いただけると、より詳しい状況がわかるので、具体的な回答を得やすくなると思います。
8
+
9
+
10
+
11
+ ---
12
+
13
+
14
+
15
+ 情報の追記、ありがとうございます。
16
+
17
+
18
+
19
+ 一見したところ、インデックスのつけ方は問題ないように見受けます。
20
+
21
+
22
+
23
+ こちらで15万件ものデータを投入した環境を用意することができないので、
24
+
25
+ 以下に、同様の結果を得られ（ると思う）、かつパフォーマンスの改善が見込めそうなクエリを
26
+
27
+ いくつか挙げさせていただきます。
28
+
29
+
30
+
31
+ #### ケース1 : JOINする順番を変更（レコードの少ないテーブルからJOIN）
32
+
33
+ ```sql
34
+
35
+ SELECT t_syumi.syumi_id, syumi_name, COUNT(DISTINCT pet_id) AS cnt
36
+
37
+ FROM t_syumi INNER JOIN t_setting ON t_syumi.syumi_id = t_setting.syumi_id
38
+
39
+ INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id
40
+
41
+ GROUP BY t_syumi.syumi_id
42
+
43
+ ORDER BY cnt DESC
44
+
45
+ ;
46
+
47
+ ```
48
+
49
+
50
+
51
+ #### ケース2 : ケース1に加えて、`DISTINCT`句を排除
52
+
53
+ ```sql
54
+
55
+ SELECT t_syumi.syumi_id, syumi_name, COUNT(pet_id) AS cnt
56
+
57
+ FROM t_syumi
58
+
59
+ INNER JOIN t_setting ON t_syumi.syumi_id = t_setting.syumi_id
60
+
61
+ INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id
62
+
63
+ GROUP BY t_syumi.syumi_id
64
+
65
+ ORDER BY cnt DESC
66
+
67
+ ;
68
+
69
+ ```
70
+
71
+
72
+
73
+ #### ケース3 : ケース2に加えて、レコード数を絞り込んでからJOIN（hirohiro様の回答と同様）
74
+
75
+ ```sql
76
+
77
+ SELECT t_syumi.syumi_id, syumi_name, cnt
78
+
79
+ FROM t_syumi INNER JOIN (
80
+
81
+ SELECT syumi_id, COUNT(*) AS cnt FROM t_setting
82
+
83
+ INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id
84
+
85
+ GROUP BY syumi_id
86
+
87
+ ) AS tmp ON t_syumi.syumi_id = tmp.syumi_id
88
+
89
+ ORDER BY cnt DESC
90
+
91
+ ;
92
+
93
+ ```
94
+
95
+
96
+
97
+ #### ケース4 : ケース3に加えて、`FORCE INDEX`句を使用
98
+
99
+ [https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html](https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html)
100
+
101
+ ```sql
102
+
103
+ SELECT t_syumi.syumi_id, syumi_name, cnt
104
+
105
+ FROM t_syumi INNER JOIN (
106
+
107
+ SELECT syumi_id, COUNT(*) AS cnt FROM t_setting
108
+
109
+ FORCE INDEX FOR GROUP BY (syumi_id)
110
+
111
+ INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id
112
+
113
+ GROUP BY syumi_id
114
+
115
+ ) AS tmp ON t_syumi.syumi_id = tmp.syumi_id
116
+
117
+ ORDER BY cnt DESC
118
+
119
+ ;
120
+
121
+ ```
122
+
123
+
124
+
125
+ どうしても改善できない場合、苦肉の策として、Javascriptなどを使って問題のデータを非同期で取得する、とかでしょうか。。
126
+
127
+
128
+
129
+ ちなみに、ご提示のクエリだとペットを飼っている人が１人もいない趣味は結果に含まれないことになりますが、
130
+
131
+ それは問題ないでしょうか？