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

編集履歴

質問編集履歴

4

記述を間違ったので修正

2020/11/15 08:00

投稿

yonotsui
yonotsui

スコア28

title CHANGED
File without changes
body CHANGED
@@ -96,6 +96,7 @@
96
96
  KEY `jan4index` (`JAN4`),
97
97
  KEY `jan5index` (`JAN5`)
98
98
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
99
+ ```
99
100
 
100
101
  ###explain
101
102
  ```ここに言語を入力

3

explain追加

2020/11/15 08:00

投稿

yonotsui
yonotsui

スコア28

title CHANGED
File without changes
body CHANGED
@@ -96,4 +96,28 @@
96
96
  KEY `jan4index` (`JAN4`),
97
97
  KEY `jan5index` (`JAN5`)
98
98
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
99
+
100
+ ###explain
101
+ ```ここに言語を入力
102
+ //否定形
103
+ mysql> explain
104
+ -> select * from asin
105
+ -> where JAN !=''
106
+ -> limit 251063,1;
107
+ +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------+
108
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
109
+ +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------+
110
+ | 1 | SIMPLE | asin | NULL | range | jansindex | jansindex | 54 | NULL | 595973 | 100.00 | Using index condition |
111
+ +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------+
112
+
113
+ //否定形なし
114
+ mysql> explain select * from asin
115
+ -> where (JAN ='')=false
116
+ -> limit 251063,1;
117
+ +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
118
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
119
+ +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
120
+ | 1 | SIMPLE | asin | NULL | ALL | NULL | NULL | NULL | NULL | 6890143 | 100.00 | Using where |
121
+ +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
122
+ ```
99
123
  ```

2

構文をcreate tableに変更

2020/11/15 07:58

投稿

yonotsui
yonotsui

スコア28

title CHANGED
File without changes
body CHANGED
@@ -18,40 +18,82 @@
18
18
  limit 251063,1;
19
19
  ```
20
20
 
21
- ###テーブル構造
21
+ ###create table
22
22
  ```ここに言語を入力
23
- +-------------------------+----------------------+------+-----+---------+-------+
24
- | Field | Type | Null | Key | Default | Extra |
25
- +-------------------------+----------------------+------+-----+---------+-------+
26
- | ASIN | varchar(10) | NO | PRI | NULL | |
27
- | JAN | varchar(13) | NO | MUL | NULL | |
28
- ～(文字数制限に引っかかったので他の列は省略)
29
- +-------------------------+----------------------+------+-----+---------+-------+
30
- ```
31
- ###インデックス
32
- ```ここに言語を入力
33
- +-------+------------+-------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
34
- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
35
- +-------+------------+-------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
36
- | asin | 0 | PRIMARY | 1 | ASIN | A | 6878129 | NULL | NULL | | BTREE | | | YES | NULL |
37
- | asin | 1 | noc_idx | 1 | NoCommunicationTick | A | 10716 | NULL | NULL | | BTREE | | | YES | NULL |
38
- | asin | 1 | price_idx | 1 | PriceSetTime | A | 147265 | NULL | NULL | | BTREE | | | YES | NULL |
39
- | asin | 1 | product_idx | 1 | ProductItemSetTime | A | 360700 | NULL | NULL | | BTREE | | | YES | NULL |
40
- | asin | 1 | dhash_idx | 1 | DHashSetTime | A | 669944 | NULL | NULL | | BTREE | | | YES | NULL |
41
- | asin | 1 | monoratetime_idx | 1 | MonorateSetTime | A | 449498 | NULL | NULL | | BTREE | | | YES | NULL |
42
- | asin | 1 | rank_idx | 1 | Ranking1 | A | 353331 | NULL | NULL | YES | BTREE | | | YES | NULL |
43
- | asin | 1 | monorate_idx | 1 | MonorateBuyNum | A | 185 | NULL | NULL | | BTREE | | | YES | NULL |
44
- | asin | 1 | fee_monorate_idx | 1 | FeeSetTime | A | 125214 | NULL | NULL | | BTREE | | | YES | NULL |
45
- | asin | 1 | fee_monorate_idx | 2 | MonorateBuyNum | A | 491185 | NULL | NULL | | BTREE | | | YES | NULL |
46
- | asin | 1 | category_idx | 1 | CategorySetTime | A | 472006 | NULL | NULL | | BTREE | | | YES | NULL |
47
- | asin | 1 | ureteru_get_index | 1 | MonorateBuyNum | A | 214 | NULL | NULL | | BTREE | | | YES | NULL |
48
- | asin | 1 | ureteru_get_index | 2 | SettingProductItem | A | 388 | NULL | NULL | | BTREE | | | YES | NULL |
49
- | asin | 1 | ureteru_get_index | 3 | NotExit | A | 690 | NULL | NULL | | BTREE | | | YES | NULL |
50
- | asin | 1 | ureteru_get_index | 4 | ASIN | A | 6878129 | NULL | NULL | | BTREE | | | YES | NULL |
51
- | asin | 1 | jansindex | 1 | JAN | A | 310792 | NULL | NULL | | BTREE | | | YES | NULL |
52
- | asin | 1 | jan2index | 1 | JAN2 | A | 11702 | NULL | NULL | | BTREE | | | YES | NULL |
53
- | asin | 1 | jan3index | 1 | JAN3 | A | 4287 | NULL | NULL | | BTREE | | | YES | NULL |
54
- | asin | 1 | jan4index | 1 | JAN4 | A | 2616 | NULL | NULL | | BTREE | | | YES | NULL |
55
- | asin | 1 | jan5index | 1 | JAN5 | A | 1847 | NULL | NULL | | BTREE | | | YES | NULL |
56
- +-------+------------+-------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
23
+ | asin | CREATE TABLE `asin` (
24
+ `ASIN` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
25
+ `JAN` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
26
+ `Title` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
27
+ `ImageUrl` varchar(2048) COLLATE utf8mb4_general_ci DEFAULT NULL,
28
+ `FixFee` smallint(6) DEFAULT NULL,
29
+ `FeeRate` float(2,2) DEFAULT NULL,
30
+ `PriceFBA` int(11) DEFAULT NULL,
31
+ `PriceSelf` int(11) DEFAULT NULL,
32
+ `PriceCart` int(11) DEFAULT NULL,
33
+ `NotExit` bit(1) NOT NULL,
34
+ `SettingFee` bit(2) NOT NULL,
35
+ `SettingProductItem` bit(2) NOT NULL,
36
+ `SettingJAN` bit(2) NOT NULL,
37
+ `PriceSetTime` bigint(20) NOT NULL,
38
+ `ProductItemSetTime` bigint(20) NOT NULL,
39
+ `FeeSetTime` bigint(20) NOT NULL,
40
+ `SellerNum` smallint(6) DEFAULT NULL,
41
+ `HadFBA` bit(1) NOT NULL,
42
+ `SettingMonorate` bit(2) NOT NULL,
43
+ `MonorateSetTime` bigint(20) NOT NULL,
44
+ `MonorateBuyNum` smallint(6) NOT NULL,
45
+ `TitleOpt` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
46
+ `Danger` bit(1) NOT NULL,
47
+ `Claim` bit(1) NOT NULL,
48
+ `MonorateNoExitRate` float(3,3) NOT NULL,
49
+ `MonorateNoExitRateTotal` float(3,3) NOT NULL,
50
+ `SettingCategory` bit(2) NOT NULL,
51
+ `CategorySetTime` bigint(20) NOT NULL,
52
+ `CategoryHash` int(10) unsigned DEFAULT NULL,
53
+ `CategoryHash2` int(10) unsigned DEFAULT NULL,
54
+ `CategoryHash3` int(10) unsigned DEFAULT NULL,
55
+ `PriceUsedFBA` int(11) DEFAULT NULL,
56
+ `PriceUsedSelf` int(11) DEFAULT NULL,
57
+ `DHashSetTime` bigint(20) NOT NULL,
58
+ `DHash` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
59
+ `RankingCategoryHash1` int(10) unsigned DEFAULT NULL,
60
+ `RankingCategoryHash2` int(10) unsigned DEFAULT NULL,
61
+ `RankingCategoryHash3` int(10) unsigned DEFAULT NULL,
62
+ `Ranking1` int(11) DEFAULT NULL,
63
+ `Ranking2` int(11) DEFAULT NULL,
64
+ `Ranking3` int(11) DEFAULT NULL,
65
+ `PricePreFBA` int(11) DEFAULT NULL,
66
+ `PricePreSelf` int(11) DEFAULT NULL,
67
+ `PricePreUsedFBA` int(11) DEFAULT NULL,
68
+ `PricePreUsedSelf` int(11) DEFAULT NULL,
69
+ `PricePreCart` int(11) DEFAULT NULL,
70
+ `SetTypeSer` smallint(5) unsigned DEFAULT NULL,
71
+ `NoCommunicationTick` bigint(20) NOT NULL,
72
+ `Width` int(10) unsigned DEFAULT NULL,
73
+ `Height` int(10) unsigned DEFAULT NULL,
74
+ `Lenght` int(10) unsigned DEFAULT NULL,
75
+ `Weight` int(10) unsigned DEFAULT NULL,
76
+ `SizeTypeSer` smallint(5) unsigned NOT NULL,
77
+ `JAN2` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
78
+ `JAN3` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
79
+ `JAN4` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
80
+ `JAN5` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
81
+ `Model` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
82
+ PRIMARY KEY (`ASIN`),
83
+ KEY `noc_idx` (`NoCommunicationTick`),
84
+ KEY `price_idx` (`PriceSetTime`),
85
+ KEY `product_idx` (`ProductItemSetTime`),
86
+ KEY `dhash_idx` (`DHashSetTime`),
87
+ KEY `monoratetime_idx` (`MonorateSetTime`),
88
+ KEY `rank_idx` (`Ranking1`),
89
+ KEY `monorate_idx` (`MonorateBuyNum`),
90
+ KEY `fee_monorate_idx` (`FeeSetTime`,`MonorateBuyNum`),
91
+ KEY `category_idx` (`CategorySetTime`),
92
+ KEY `ureteru_get_index` (`MonorateBuyNum`,`SettingProductItem`,`NotExit`,`ASIN`),
93
+ KEY `jansindex` (`JAN`),
94
+ KEY `jan2index` (`JAN2`),
95
+ KEY `jan3index` (`JAN3`),
96
+ KEY `jan4index` (`JAN4`),
97
+ KEY `jan5index` (`JAN5`)
98
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
57
99
  ```

1

limitの記載を忘れていた

2020/11/15 07:22

投稿

yonotsui
yonotsui

スコア28

title CHANGED
File without changes
body CHANGED
@@ -9,11 +9,13 @@
9
9
  ```ここに言語を入力
10
10
  //否定形のクエリ
11
11
  select * from asin
12
- where JAN !='';
12
+ where JAN !=''
13
+ limit 251063,1;
13
14
 
14
15
  //否定形をつかわないクエリ
15
16
  select * from asin
16
- where (JAN ='')=false;
17
+ where (JAN ='')=false
18
+ limit 251063,1;
17
19
  ```
18
20
 
19
21
  ###テーブル構造