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