Re: Index suggestion needed for performance improvement
Here is another query , it was perfromaing faster previously but now its taking 2+ seconds.,
# Query_time: 2.187370 Lock_time: 0.000112 Rows_sent: 25 Rows_examined: 1525431
SET timestamp=1443853148;
SELECT content.title,
content.og_name,
content.record_num,
content.category,
content.upload_date,
content.hash,
content.comments_count,
content.verified,
content.uploader,
content.size,
content.seeders,
content.leechers FROM content WHERE enabled = 1 AND category IN (800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820) ORDER BY seeders DESC , upload_date DESC LIMIT 0,25;
mysql> explain SELECT content.title,content.og_name,content.record_num,content.category,content.upload_date,content.hash,content.comments_count,content.verified,content.uploader,content.size,content.seeders,content.leechers FROM content WHERE enabled = 1 AND category IN (800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820) ORDER BY seeders DESC , upload_date DESC LIMIT 0,25\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: content
type: range
possible_keys: e_c_d_v,e_d_v,e_c_v,e_v,e_u,enabled_upload_date_seeders,enabled_upload_date_leechers,enabled_category_seeders_upload_date
key: e_c_d_v
key_len: 3
ref: NULL
rows: 11508
Extra: Using index condition; Using filesort
1 row in set (0.00 sec)
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 `enabled_upload_date_seeders` (`enabled`,`upload_date`,`seeders`),
KEY `enabled_upload_date_leechers` (`enabled`,`upload_date`,`leechers`),
KEY `anonymous_uploader` (`anonymous`,`uploader`),
KEY `enabled_category_seeders_upload_date` (`enabled`,`category`,`seeders`,`upload_date`)
) ENGINE=InnoDB AUTO_INCREMENT=7095220 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED |
I added
enabled_upload_date_seeders
enabled_upload_date_leechers
enabled_category_seeders_upload_date
indexes, but still query is slow ., any idea ?