teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問MySQL:文字列が空文字ではない行を調べるクエリを高速化する方法

編集履歴

質問編集履歴

4

記述を間違ったので修正

2020/11/15 08:00

投稿

yonotsui
yonotsui

スコア28

test CHANGED
File without changes
test CHANGED
@@ -194,6 +194,8 @@
194
194
 
195
195
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
196
196
 
197
+ ```
198
+
197
199
 
198
200
 
199
201
  ###explain

3

explain追加

2020/11/15 08:00

投稿

yonotsui
yonotsui

スコア28

test CHANGED
File without changes
test CHANGED
@@ -194,4 +194,52 @@
194
194
 
195
195
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
196
196
 
197
+
198
+
199
+ ###explain
200
+
201
+ ```ここに言語を入力
202
+
203
+ //否定形
204
+
205
+ mysql> explain
206
+
207
+ -> select * from asin
208
+
209
+ -> where JAN !=''
210
+
211
+ -> limit 251063,1;
212
+
213
+ +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------+
214
+
215
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
216
+
217
+ +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------+
218
+
219
+ | 1 | SIMPLE | asin | NULL | range | jansindex | jansindex | 54 | NULL | 595973 | 100.00 | Using index condition |
220
+
221
+ +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------+
222
+
223
+
224
+
225
+ //否定形なし
226
+
227
+ mysql> explain select * from asin
228
+
229
+ -> where (JAN ='')=false
230
+
231
+ -> limit 251063,1;
232
+
233
+ +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
234
+
235
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
236
+
237
+ +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
238
+
239
+ | 1 | SIMPLE | asin | NULL | ALL | NULL | NULL | NULL | NULL | 6890143 | 100.00 | Using where |
240
+
241
+ +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
242
+
197
243
  ```
244
+
245
+ ```

2

構文をcreate tableに変更

2020/11/15 07:58

投稿

yonotsui
yonotsui

スコア28

test CHANGED
File without changes
test CHANGED
@@ -38,76 +38,160 @@
38
38
 
39
39
 
40
40
 
41
- ###テーブル構造
41
+ ###create table
42
42
 
43
43
  ```ここに言語を入力
44
44
 
45
- +-------------------------+----------------------+------+-----+---------+-------+
45
+ | asin | CREATE TABLE `asin` (
46
46
 
47
- | Field | Type | Null | Key | Default | Extra |
47
+ `ASIN` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
48
48
 
49
- +-------------------------+----------------------+------+-----+---------+-------+
49
+ `JAN` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
50
50
 
51
- | ASIN | varchar(10) | NO | PRI | NULL | |
51
+ `Title` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
52
52
 
53
- | JAN | varchar(13) | NO | MUL | NULL | |
53
+ `ImageUrl` varchar(2048) COLLATE utf8mb4_general_ci DEFAULT NULL,
54
54
 
55
- ～(文字数制限に引っかかったので他の列は省略)
55
+ `FixFee` smallint(6) DEFAULT NULL,
56
56
 
57
+ `FeeRate` float(2,2) DEFAULT NULL,
58
+
59
+ `PriceFBA` int(11) DEFAULT NULL,
60
+
61
+ `PriceSelf` int(11) DEFAULT NULL,
62
+
63
+ `PriceCart` int(11) DEFAULT NULL,
64
+
65
+ `NotExit` bit(1) NOT NULL,
66
+
67
+ `SettingFee` bit(2) NOT NULL,
68
+
69
+ `SettingProductItem` bit(2) NOT NULL,
70
+
71
+ `SettingJAN` bit(2) NOT NULL,
72
+
73
+ `PriceSetTime` bigint(20) NOT NULL,
74
+
75
+ `ProductItemSetTime` bigint(20) NOT NULL,
76
+
77
+ `FeeSetTime` bigint(20) NOT NULL,
78
+
79
+ `SellerNum` smallint(6) DEFAULT NULL,
80
+
81
+ `HadFBA` bit(1) NOT NULL,
82
+
83
+ `SettingMonorate` bit(2) NOT NULL,
84
+
85
+ `MonorateSetTime` bigint(20) NOT NULL,
86
+
87
+ `MonorateBuyNum` smallint(6) NOT NULL,
88
+
89
+ `TitleOpt` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
90
+
91
+ `Danger` bit(1) NOT NULL,
92
+
93
+ `Claim` bit(1) NOT NULL,
94
+
95
+ `MonorateNoExitRate` float(3,3) NOT NULL,
96
+
97
+ `MonorateNoExitRateTotal` float(3,3) NOT NULL,
98
+
99
+ `SettingCategory` bit(2) NOT NULL,
100
+
101
+ `CategorySetTime` bigint(20) NOT NULL,
102
+
103
+ `CategoryHash` int(10) unsigned DEFAULT NULL,
104
+
105
+ `CategoryHash2` int(10) unsigned DEFAULT NULL,
106
+
107
+ `CategoryHash3` int(10) unsigned DEFAULT NULL,
108
+
109
+ `PriceUsedFBA` int(11) DEFAULT NULL,
110
+
111
+ `PriceUsedSelf` int(11) DEFAULT NULL,
112
+
113
+ `DHashSetTime` bigint(20) NOT NULL,
114
+
115
+ `DHash` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
116
+
117
+ `RankingCategoryHash1` int(10) unsigned DEFAULT NULL,
118
+
119
+ `RankingCategoryHash2` int(10) unsigned DEFAULT NULL,
120
+
121
+ `RankingCategoryHash3` int(10) unsigned DEFAULT NULL,
122
+
123
+ `Ranking1` int(11) DEFAULT NULL,
124
+
125
+ `Ranking2` int(11) DEFAULT NULL,
126
+
127
+ `Ranking3` int(11) DEFAULT NULL,
128
+
129
+ `PricePreFBA` int(11) DEFAULT NULL,
130
+
131
+ `PricePreSelf` int(11) DEFAULT NULL,
132
+
133
+ `PricePreUsedFBA` int(11) DEFAULT NULL,
134
+
135
+ `PricePreUsedSelf` int(11) DEFAULT NULL,
136
+
137
+ `PricePreCart` int(11) DEFAULT NULL,
138
+
139
+ `SetTypeSer` smallint(5) unsigned DEFAULT NULL,
140
+
141
+ `NoCommunicationTick` bigint(20) NOT NULL,
142
+
143
+ `Width` int(10) unsigned DEFAULT NULL,
144
+
145
+ `Height` int(10) unsigned DEFAULT NULL,
146
+
147
+ `Lenght` int(10) unsigned DEFAULT NULL,
148
+
149
+ `Weight` int(10) unsigned DEFAULT NULL,
150
+
151
+ `SizeTypeSer` smallint(5) unsigned NOT NULL,
152
+
153
+ `JAN2` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
154
+
155
+ `JAN3` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
156
+
157
+ `JAN4` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
158
+
159
+ `JAN5` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
160
+
161
+ `Model` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
162
+
163
+ PRIMARY KEY (`ASIN`),
164
+
165
+ KEY `noc_idx` (`NoCommunicationTick`),
166
+
167
+ KEY `price_idx` (`PriceSetTime`),
168
+
169
+ KEY `product_idx` (`ProductItemSetTime`),
170
+
171
+ KEY `dhash_idx` (`DHashSetTime`),
172
+
173
+ KEY `monoratetime_idx` (`MonorateSetTime`),
174
+
175
+ KEY `rank_idx` (`Ranking1`),
176
+
177
+ KEY `monorate_idx` (`MonorateBuyNum`),
178
+
179
+ KEY `fee_monorate_idx` (`FeeSetTime`,`MonorateBuyNum`),
180
+
181
+ KEY `category_idx` (`CategorySetTime`),
182
+
57
- +-------------------------+----------------------+------+-----+---------+-------+
183
+ KEY `ureteru_get_index` (`MonorateBuyNum`,`SettingProductItem`,`NotExit`,`ASIN`),
184
+
185
+ KEY `jansindex` (`JAN`),
186
+
187
+ KEY `jan2index` (`JAN2`),
188
+
189
+ KEY `jan3index` (`JAN3`),
190
+
191
+ KEY `jan4index` (`JAN4`),
192
+
193
+ KEY `jan5index` (`JAN5`)
194
+
195
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
58
196
 
59
197
  ```
60
-
61
- ###インデックス
62
-
63
- ```ここに言語を入力
64
-
65
- +-------+------------+-------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
66
-
67
- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
68
-
69
- +-------+------------+-------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
70
-
71
- | asin | 0 | PRIMARY | 1 | ASIN | A | 6878129 | NULL | NULL | | BTREE | | | YES | NULL |
72
-
73
- | asin | 1 | noc_idx | 1 | NoCommunicationTick | A | 10716 | NULL | NULL | | BTREE | | | YES | NULL |
74
-
75
- | asin | 1 | price_idx | 1 | PriceSetTime | A | 147265 | NULL | NULL | | BTREE | | | YES | NULL |
76
-
77
- | asin | 1 | product_idx | 1 | ProductItemSetTime | A | 360700 | NULL | NULL | | BTREE | | | YES | NULL |
78
-
79
- | asin | 1 | dhash_idx | 1 | DHashSetTime | A | 669944 | NULL | NULL | | BTREE | | | YES | NULL |
80
-
81
- | asin | 1 | monoratetime_idx | 1 | MonorateSetTime | A | 449498 | NULL | NULL | | BTREE | | | YES | NULL |
82
-
83
- | asin | 1 | rank_idx | 1 | Ranking1 | A | 353331 | NULL | NULL | YES | BTREE | | | YES | NULL |
84
-
85
- | asin | 1 | monorate_idx | 1 | MonorateBuyNum | A | 185 | NULL | NULL | | BTREE | | | YES | NULL |
86
-
87
- | asin | 1 | fee_monorate_idx | 1 | FeeSetTime | A | 125214 | NULL | NULL | | BTREE | | | YES | NULL |
88
-
89
- | asin | 1 | fee_monorate_idx | 2 | MonorateBuyNum | A | 491185 | NULL | NULL | | BTREE | | | YES | NULL |
90
-
91
- | asin | 1 | category_idx | 1 | CategorySetTime | A | 472006 | NULL | NULL | | BTREE | | | YES | NULL |
92
-
93
- | asin | 1 | ureteru_get_index | 1 | MonorateBuyNum | A | 214 | NULL | NULL | | BTREE | | | YES | NULL |
94
-
95
- | asin | 1 | ureteru_get_index | 2 | SettingProductItem | A | 388 | NULL | NULL | | BTREE | | | YES | NULL |
96
-
97
- | asin | 1 | ureteru_get_index | 3 | NotExit | A | 690 | NULL | NULL | | BTREE | | | YES | NULL |
98
-
99
- | asin | 1 | ureteru_get_index | 4 | ASIN | A | 6878129 | NULL | NULL | | BTREE | | | YES | NULL |
100
-
101
- | asin | 1 | jansindex | 1 | JAN | A | 310792 | NULL | NULL | | BTREE | | | YES | NULL |
102
-
103
- | asin | 1 | jan2index | 1 | JAN2 | A | 11702 | NULL | NULL | | BTREE | | | YES | NULL |
104
-
105
- | asin | 1 | jan3index | 1 | JAN3 | A | 4287 | NULL | NULL | | BTREE | | | YES | NULL |
106
-
107
- | asin | 1 | jan4index | 1 | JAN4 | A | 2616 | NULL | NULL | | BTREE | | | YES | NULL |
108
-
109
- | asin | 1 | jan5index | 1 | JAN5 | A | 1847 | NULL | NULL | | BTREE | | | YES | NULL |
110
-
111
- +-------+------------+-------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
112
-
113
- ```

1

limitの記載を忘れていた

2020/11/15 07:22

投稿

yonotsui
yonotsui

スコア28

test CHANGED
File without changes
test CHANGED
@@ -20,7 +20,9 @@
20
20
 
21
21
  select * from asin
22
22
 
23
- where JAN !='';
23
+ where JAN !=''
24
+
25
+ limit 251063,1;
24
26
 
25
27
 
26
28
 
@@ -28,7 +30,9 @@
28
30
 
29
31
  select * from asin
30
32
 
31
- where (JAN ='')=false;
33
+ where (JAN ='')=false
34
+
35
+ limit 251063,1;
32
36
 
33
37
  ```
34
38