MySQL Forums
Forum List  »  Performance

Re: Index suggestion needed for performance improvement
Posted by: Ronald Brown
Date: October 03, 2015 12:40AM

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 ?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Index suggestion needed for performance improvement
832
October 03, 2015 12:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.