Re: Why is ORDER BY ASC is slow but ORDER DESC is fast ? and how can i fix it ?
CREATE TABLE `content` (
`hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`og_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`keywords` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`files_count` smallint(5) unsigned NOT NULL DEFAULT '0',
`more_files` smallint(5) unsigned NOT NULL DEFAULT '0',
`files` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`category` smallint(3) unsigned NOT NULL DEFAULT '600',
`size` bigint(19) unsigned NOT NULL DEFAULT '0',
`leechers` int(11) NOT NULL DEFAULT '0',
`completed` int(11) NOT NULL DEFAULT '0',
`seeders` int(11) NOT NULL DEFAULT '0',
`creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`upload_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`vote_up` int(11) unsigned NOT NULL DEFAULT '0',
`vote_down` int(11) unsigned NOT NULL DEFAULT '0',
`comments_count` int(11) NOT NULL DEFAULT '0',
`imdb` int(8) unsigned NOT NULL DEFAULT '0',
`video_sample` tinyint(1) NOT NULL DEFAULT '0',
`video_quality` tinyint(2) NOT NULL DEFAULT '0',
`audio_lang` varchar(127) CHARACTER SET ascii NOT NULL DEFAULT '',
`subtitle_lang` varchar(127) CHARACTER SET ascii NOT NULL DEFAULT '',
`verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
`uploader` int(11) unsigned NOT NULL DEFAULT '0',
`anonymous` tinyint(1) NOT NULL DEFAULT '0',
`enabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
`tfile_size` int(11) unsigned NOT NULL DEFAULT '0',
`scrape_source` tinyint(1) unsigned NOT NULL DEFAULT '0',
`record_num` int(11) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`record_num`),
UNIQUE KEY `hash` (`hash`),
KEY `upload_date` (`upload_date`),
KEY `seeders` (`seeders`),
KEY `leechers` (`leechers`),
KEY `vote_up` (`vote_up`),
KEY `comments_count` (`comments_count`),
KEY `tfile_size` (`tfile_size`),
KEY `e_c_d_v` (`enabled`,`category`,`upload_date`,`verified`),
KEY `e_d_v` (`enabled`,`upload_date`,`verified`),
KEY `e_c_v` (`enabled`,`category`,`verified`),
KEY `e_v` (`enabled`,`verified`),
KEY `e_u` (`enabled`,`uploader`),
KEY `size` (`size`),
KEY `anonymous_uploader` (`anonymous`,`uploader`),
KEY `e_s_d` (`enabled`,`seeders`,`upload_date`)
) ENGINE=InnoDB AUTO_INCREMENT=7098840 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED |
this takes 0.08 secseconds
mysql> explain SELECT * FROM content WHERE enabled = 1 AND category IN (600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620) AND upload_date >= '2015-08-31' ORDER BY comments_count ASC LIMIT 900,25\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: content
type: index
possible_keys: upload_date,e_c_d_v,e_d_v,e_c_v,e_v,e_u,e_s_d
key: comments_count
key_len: 4
ref: NULL
rows: 188516
Extra: Using where
1 row in set (0.00 sec)
this takes 12.xx seconds
mysql> explain SELECT * FROM content WHERE enabled = 1 AND category IN (600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620) AND upload_date >= '2015-08-31' ORDER BY comments_count DESC LIMIT 900,25\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: content
type: index
possible_keys: upload_date,e_c_d_v,e_d_v,e_c_v,e_v,e_u,e_s_d
key: comments_count
key_len: 4
ref: NULL
rows: 188516
Extra: Using where
1 row in set (0.01 sec)
FLUSH STATUS;
SELECT ... ASC
SHOW SESSION STATUS LIKE 'Handler%';
mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 6799069 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+---------+
18 rows in set (0.00 sec)
FLUSH STATUS;
SELECT ... DESC
SHOW SESSION STATUS LIKE 'Handler%';
mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 25635 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
there already indexes hvaing enabled and category i.e.
KEY `e_c_d_v` (`enabled`,`category`,`upload_date`,`verified`)
KEY `e_c_v` (`enabled`,`category`,`verified`)