Laravel5.3でのページネーションするために発行されるクエリ"select count(*) as aggregate from"の発行時間を短縮して高速化したいです。where句で使用されるカラムstats_per_day
とchecked_at
はインデックスを貼っております。具体的なクエリのログは以下になります。二番目のクエリは14ミリ秒しかかかっていないのに対して、最初のクエリが1616ミリ秒もかかってしまっております。
[1] => Array ( [query] => select count(*) as aggregate from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > ? [bindings] => Array ( [0] => Carbon\Carbon Object ( [date] => 2016-12-13 07:11:25.000000 [timezone_type] => 3 [timezone] => UTC ) ) [time] => 1616.25 ) [2] => Array ( [query] => select * from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > ? order by `stars_per_day` desc limit 10 offset 0 [bindings] => Array ( [0] => Carbon\Carbon Object ( [date] => 2016-12-13 07:11:25.000000 [timezone_type] => 3 [timezone] => UTC ) ) [time] => 14.98 )
###追加情報1
データーベース情報は以下のようになりました。何故かMySQLクライアントからだと高速に発行できます
Mysql
1 2mysql> 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'; 3+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+-----------------------------------------------+ 4| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 5+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+-----------------------------------------------+ 6| 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 | 7+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+-----------------------------------------------+ 81 row in set, 1 warning (0.00 sec) 9 10 11mysql> 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; 12+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+------------------------------------+ 13| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 14+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+------------------------------------+ 15| 1 | SIMPLE | repositories | NULL | range | stars_per_day,checked_at | stars_per_day | 5 | NULL | 16287 | 50.00 | Using index condition; Using where | 16+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+------------------------------------+ 171 row in set, 1 warning (0.01 sec) 18 19 20mysql> show create table repositories; 21| repositories | CREATE TABLE `repositories` ( 22 `id` int(11) NOT NULL AUTO_INCREMENT, 23 `repository_id` int(11) NOT NULL, 24 `full_name` varchar(255) DEFAULT NULL, 25 `name` varchar(255) DEFAULT NULL, 26 `master_branch` varchar(255) DEFAULT NULL, 27 `readme` longtext, 28 `stargazers_count` int(11) DEFAULT NULL, 29 `language_id` int(11) DEFAULT NULL, 30 `description` longtext, 31 `description_ja` longtext, 32 `homepage` longtext, 33 `created_at` datetime DEFAULT NULL, 34 `updated_at` datetime DEFAULT NULL, 35 `pushed_at` datetime DEFAULT NULL, 36 `stars_per_day` float DEFAULT NULL, 37 `similar_repositories` longtext, 38 `checked_at` datetime DEFAULT NULL, 39 `avatar_url` longtext, 40 PRIMARY KEY (`id`), 41 UNIQUE KEY `repositories_repository_id` (`repository_id`), 42 KEY `stars_per_day` (`stars_per_day`), 43 KEY `checked_at` (`checked_at`), 44 FULLTEXT KEY `repositories` (`full_name`,`name`,`description`) 45) ENGINE=InnoDB AUTO_INCREMENT=45097 DEFAULT CHARSET=utf8mb4 |
###追加情報2
offsetの数が大きくなるほど遅くなります
offsetの数が49700の場合は6秒もかかってしまいました
MySQL
1mysql> 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; 210 rows in set (6.50 sec)
offsetの数が0の場合は爆速です
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; 10 rows in set (0.01 sec)
Extraは変わりありませんでした
force indexを付け加えた場合
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; +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ | 1 | SIMPLE | repositories | NULL | range | idx_1 | idx_1 | 5 | NULL | 19628 | 33.33 | Using index condition | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
force indexを付け加えない場合
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; +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ | 1 | SIMPLE | repositories | NULL | range | idx_1,idx_2 | idx_1 | 5 | NULL | 19628 | 50.00 | Using index condition | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
回答3件
あなたの回答
tips
プレビュー