MySQL Forums
Forum List  »  Performance

Index suggestion needed for performance improvement
Posted by: Ronald Brown
Date: September 23, 2015 11:49AM

I have cronjob run these queries every few minutes .and these queries ended up in slow queries log.

1) #########################################################
# Query_time: 5.680377 Lock_time: 0.000141 Rows_sent: 0 Rows_examined: 2
SET timestamp=1442342167;

UPDATE users u,( SELECT count(content.record_num) as pdfs_uploaded_count, content.uploader as uploader
FROM users
LEFT JOIN content
ON users.record_num = content.uploader
WHERE content.anonymous = 0
AND content.uploader != 0
GROUP BY users.record_num) as s
SET u.pdfs_uploaded = s.pdfs_uploaded_count
WHERE u.record_num = s.uploader;

mysql> explain UPDATE users u,(SELECT count(content.record_num) as pdfs_uploaded_count, content.uploader as uploader
-> FROM users
-> LEFT JOIN content
-> ON users.record_num = content.uploader
-> WHERE content.anonymous = 0
-> AND content.uploader != 0
-> GROUP BY users.record_num) as s
-> SET u.pdfs_uploaded = s.pdfs_uploaded_count
-> WHERE u.record_num = s.uploader\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: u
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 5
ref: basesite.u.record_num
rows: 62308
Extra: Using where
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: content
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6230854
Extra: Using where; Using temporary; Using filesort
*************************** 4. row ***************************
id: 2
select_type: DERIVED
table: users
type: eq_ref
possible_keys: PRIMARY,username,email,password,signup_verification
key: PRIMARY
key_len: 4
ref: basesite.content.uploader
rows: 1
Extra: Using where; Using index
4 rows in set (0.00 sec)



CREATE TABLE `users` (
`username` char(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`password` char(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`email` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`first_name` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`last_name` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`provider` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`provider_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`last_ip` char(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`avatar_ext` tinyint(1) NOT NULL DEFAULT '0',
`date_joined` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`enabled` tinyint(1) NOT NULL DEFAULT '1',
`lastlogin` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`gender` tinyint(1) NOT NULL DEFAULT '0',
`status_message` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`status_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`pdfs_favorites` int(11) NOT NULL DEFAULT '0',
`pdfs_rss` int(11) NOT NULL DEFAULT '0',
`pdfs_uploaded` int(11) NOT NULL DEFAULT '0',
`signup_verification` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`forgot_password_verification` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`rss_hash` char(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`verified` tinyint(1) NOT NULL DEFAULT '0',
`record_num` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`record_num`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`),
KEY `password` (`password`),
KEY `signup_verification` (`signup_verification`)
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE `content` (
`hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
`title` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`og_name` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`keywords` char(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` char(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 `e_s_ud` (`enabled`,`seeders`,`upload_date`),
KEY `size` (`size`)
) ENGINE=InnoDB AUTO_INCREMENT=7050569 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED


please suggest me index to create.
thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Index suggestion needed for performance improvement
1543
September 23, 2015 11:49AM


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.