teratail header banner
teratail header banner
質問するログイン新規登録

質問編集履歴

2

追加情報

2016/12/15 12:33

投稿

cat_breed
cat_breed

スコア123

title CHANGED
File without changes
body CHANGED
@@ -36,7 +36,7 @@
36
36
  )
37
37
  ```
38
38
 
39
- ###追加情報
39
+ ###追加情報1
40
40
 
41
41
  データーベース情報は以下のようになりました。何故かMySQLクライアントからだと高速に発行できます
42
42
  ```Mysql
@@ -85,4 +85,43 @@
85
85
  KEY `checked_at` (`checked_at`),
86
86
  FULLTEXT KEY `repositories` (`full_name`,`name`,`description`)
87
87
  ) ENGINE=InnoDB AUTO_INCREMENT=45097 DEFAULT CHARSET=utf8mb4 |
88
+ ```
89
+
90
+ ###追加情報2
91
+ **offsetの数が大きくなるほど遅くなります**
92
+
93
+ offsetの数が49700の場合は6秒もかかってしまいました
94
+ ```MySQL
95
+ mysql> select * from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 11:59:34.000000' order by `stars_per_day` desc limit 10 offset 49700;
96
+ 10 rows in set (6.50 sec)
97
+ ```
98
+
99
+ offsetの数が0の場合は爆速です
100
+ ```ここに言語を入力
101
+ mysql> select * from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 11:59:34.000000' order by `stars_per_day` desc limit 10 offset 0;
102
+ 10 rows in set (0.01 sec)
103
+ ```
104
+
105
+ **Extraは変わりありませんでした**
106
+
107
+ force indexを付け加えた場合
108
+ ```ここに言語を入力
109
+ mysql> explain select * from `repositories` force index (idx_1) where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 11:59:34.000000' order by `stars_per_day` desc limit 10 offset 49700;
110
+ +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+
111
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
112
+ +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+
113
+ | 1 | SIMPLE | repositories | NULL | range | idx_1 | idx_1 | 5 | NULL | 19628 | 33.33 | Using index condition |
114
+ +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+
115
+ 1 row in set, 1 warning (0.00 sec)
116
+ ```
117
+
118
+ force indexを付け加えない場合
119
+ ```ここに言語を入力
120
+ mysql> explain select * from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 11:59:34.000000' order by `stars_per_day` desc limit 10 offset 49700;
121
+ +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+
122
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
123
+ +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+
124
+ | 1 | SIMPLE | repositories | NULL | range | idx_1,idx_2 | idx_1 | 5 | NULL | 19628 | 50.00 | Using index condition |
125
+ +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+
126
+ 1 row in set, 1 warning (0.00 sec)
88
127
  ```

1

情報追加

2016/12/15 12:33

投稿

cat_breed
cat_breed

スコア123

title CHANGED
File without changes
body CHANGED
@@ -34,4 +34,55 @@
34
34
 
35
35
  [time] => 14.98
36
36
  )
37
+ ```
38
+
39
+ ###追加情報
40
+
41
+ データーベース情報は以下のようになりました。何故かMySQLクライアントからだと高速に発行できます
42
+ ```Mysql
43
+
44
+ mysql> EXPLAIN select count(*) as aggregate from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 07:48:38.000000';
45
+ +----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+-----------------------------------------------+
46
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
47
+ +----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+-----------------------------------------------+
48
+ | 1 | SIMPLE | repositories | NULL | range | stars_per_day,checked_at | stars_per_day | 5 | NULL | 16287 | 50.00 | Using index condition; Using where; Using MRR |
49
+ +----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+-----------------------------------------------+
50
+ 1 row in set, 1 warning (0.00 sec)
51
+
52
+
53
+ mysql> EXPLAIN select * from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 07:48:38.000000' order by `stars_per_day` desc limit 10 offset 0;
54
+ +----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+------------------------------------+
55
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
56
+ +----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+------------------------------------+
57
+ | 1 | SIMPLE | repositories | NULL | range | stars_per_day,checked_at | stars_per_day | 5 | NULL | 16287 | 50.00 | Using index condition; Using where |
58
+ +----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+------------------------------------+
59
+ 1 row in set, 1 warning (0.01 sec)
60
+
61
+
62
+ mysql> show create table repositories;
63
+ | repositories | CREATE TABLE `repositories` (
64
+ `id` int(11) NOT NULL AUTO_INCREMENT,
65
+ `repository_id` int(11) NOT NULL,
66
+ `full_name` varchar(255) DEFAULT NULL,
67
+ `name` varchar(255) DEFAULT NULL,
68
+ `master_branch` varchar(255) DEFAULT NULL,
69
+ `readme` longtext,
70
+ `stargazers_count` int(11) DEFAULT NULL,
71
+ `language_id` int(11) DEFAULT NULL,
72
+ `description` longtext,
73
+ `description_ja` longtext,
74
+ `homepage` longtext,
75
+ `created_at` datetime DEFAULT NULL,
76
+ `updated_at` datetime DEFAULT NULL,
77
+ `pushed_at` datetime DEFAULT NULL,
78
+ `stars_per_day` float DEFAULT NULL,
79
+ `similar_repositories` longtext,
80
+ `checked_at` datetime DEFAULT NULL,
81
+ `avatar_url` longtext,
82
+ PRIMARY KEY (`id`),
83
+ UNIQUE KEY `repositories_repository_id` (`repository_id`),
84
+ KEY `stars_per_day` (`stars_per_day`),
85
+ KEY `checked_at` (`checked_at`),
86
+ FULLTEXT KEY `repositories` (`full_name`,`name`,`description`)
87
+ ) ENGINE=InnoDB AUTO_INCREMENT=45097 DEFAULT CHARSET=utf8mb4 |
37
88
  ```