Heres my query:
SELECT `users`.`id`, `users`.`username`, `users`.`profile_picture_url`, `users`.`credits_offered`, `users`.`country_id`, `users`.`user_level`, `users`.`credits`
FROM `users`
LEFT JOIN `history` ON `history`.`actor_id` = 100 AND `history`.`receiver_id` = `users`.`id`
LEFT JOIN `blocked` ON `blocked`.`actor_id` = 100 AND `blocked`.`receiver_id` = `users`.`id`
WHERE `history`.`receiver_id` IS NULL
AND `blocked`.`receiver_id` IS NULL
AND `users`.`instagram_active` = 1
AND `users`.`banned` = 0
AND `users`.`shown_to_others` = 1
AND `users`.`featured_user` = 0
AND `users`.`id` IN(
SELECT `user_id` FROM `interests` WHERE `interest` IN(
SELECT `interest` FROM `interests` WHERE `user_id` = 100 AND `active` = 1))
AND `users`.`id` <> 100
AND `users`.`featured_user` = 0
ORDER BY (`users`.`credits` >= `users`.`credits_offered`) DESC, `users`.`credits_offered` DESC, `users`.`credits` DESC
LIMIT 0, 25
Users Table:
CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`instagram_id` int(11) NOT NULL,
`username` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`bio` text COLLATE utf8_unicode_ci,
`website` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`profile_picture_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`full_name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`media_count` int(11) unsigned NOT NULL,
`followed_by_count` int(11) unsigned NOT NULL,
`follows_count` int(11) unsigned NOT NULL,
`last_updated` datetime NOT NULL,
`last_updated_instagram` datetime NOT NULL,
`instagram_active` tinyint(1) DEFAULT NULL,
`last_login` datetime NOT NULL,
`inserted_on` datetime NOT NULL,
`banned` tinyint(1) NOT NULL DEFAULT '0',
`banned_reason` text COLLATE utf8_unicode_ci,
`oauth_token` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`user_level` tinyint(4) NOT NULL,
`shown_to_others` tinyint(1) NOT NULL DEFAULT '1',
`credits_offered` tinyint(1) unsigned NOT NULL DEFAULT '2',
`active` tinyint(1) NOT NULL DEFAULT '1',
`email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`registered_ip` varchar(17) COLLATE utf8_unicode_ci DEFAULT NULL,
`credits` int(11) NOT NULL,
`email_notifications` tinyint(1) NOT NULL DEFAULT '1',
`todays_followers` int(11) NOT NULL DEFAULT '0',
`todays_followers_hour` int(11) NOT NULL,
`total_followers` int(11) NOT NULL,
`credits_yesterday` int(11) NOT NULL,
`email_is_verified` tinyint(1) NOT NULL DEFAULT '0',
`email_announcements` tinyint(1) NOT NULL DEFAULT '1',
`email_credits` tinyint(1) NOT NULL DEFAULT '1',
`verification_code` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
`country_id` bigint(20) unsigned DEFAULT NULL,
`browser_info_id` bigint(20) unsigned DEFAULT NULL,
`featured_user` tinyint(1) NOT NULL DEFAULT '0',
`emailed_credits` tinyint(1) NOT NULL DEFAULT '0',
`followers_same_interests` tinyint(1) NOT NULL DEFAULT '0',
UNIQUE KEY `id` (`id`),
UNIQUE KEY `instagram_id` (`instagram_id`),
KEY `country_id` (`country_id`),
KEY `browser_info_id` (`browser_info_id`),
KEY `username` (`username`,`instagram_active`,`banned`),
CONSTRAINT `users_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `users_ibfk_2` FOREIGN KEY (`browser_info_id`) REFERENCES `browser_info` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3524 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Interests Table:
CREATE TABLE `interests` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`interest` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`inserted_dt` datetime NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
UNIQUE KEY `id` (`id`),
KEY `user_id` (`user_id`),
KEY `interest` (`interest`),
CONSTRAINT `interests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11828 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Blocked Table:
CREATE TABLE `blocked` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`actor_id` bigint(20) unsigned NOT NULL,
`receiver_id` bigint(20) unsigned DEFAULT NULL,
`blocked_reason` enum('Skipped','Blocked') COLLATE utf8_unicode_ci NOT NULL,
`inserted_dt` datetime NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`browser_info_id` bigint(20) unsigned DEFAULT NULL,
UNIQUE KEY `id` (`id`),
KEY `actor_id` (`actor_id`,`receiver_id`),
KEY `receiver_id` (`receiver_id`),
KEY `browser_info_id` (`browser_info_id`),
CONSTRAINT `blocked_ibfk_1` FOREIGN KEY (`actor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `blocked_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `blocked_ibfk_3` FOREIGN KEY (`browser_info_id`) REFERENCES `browser_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=35800 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
History Table:
CREATE TABLE `history` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`actor_id` bigint(20) unsigned NOT NULL,
`receiver_id` bigint(20) unsigned NOT NULL,
`credits` int(11) NOT NULL,
`dt` datetime NOT NULL,
`browser_info_id` bigint(20) unsigned DEFAULT NULL,
UNIQUE KEY `id` (`id`),
KEY `actor_id` (`actor_id`,`receiver_id`),
KEY `browser_info_id` (`browser_info_id`),
KEY `receiver_id` (`receiver_id`),
CONSTRAINT `history_ibfk_1` FOREIGN KEY (`browser_info_id`) REFERENCES `browser_info` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `history_ibfk_5` FOREIGN KEY (`actor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `history_ibfk_6` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=148552 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
I run my query on the profiler and this is what I get:
Status Time
starting 0.000009
Waiting for query cache lock 0.000003
Waiting on query cache mutex 0.000003
checking query cache for query 0.000150
checking permissions 0.000004
checking permissions 0.000001
checking permissions 0.000002
checking permissions 0.000002
checking permissions 0.000004
Opening tables 0.000113
System lock 0.000018
Waiting for query cache lock 0.000003
Waiting on query cache mutex 0.000068
init 0.000122
optimizing 0.000031
statistics 0.000171
preparing 0.000070
executing 0.000003
Sorting result 0.000096
optimizing 0.000007
statistics 0.000010
preparing 0.000146
optimizing 0.000006
statistics 0.000025
preparing 7.110198
Sending data 0.000589
end 0.000009
query end 0.000011
closing tables 0.000070
freeing items 0.000045
logging slow query 0.000003
cleaning up 0.000014
So it takes 7.11 seconds on average "preparing" this query. This is WAY too long and I feel it could be shortened if I was better at SQL. Can someone help me? I have added all the tables need to figure it out. Was stuck on this for some time.
Explain: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY users range id id 8 NULL 1880 Using where; Using filesort 1 PRIMARY history ref actor_id,receiver_id actor_id 16 const,bradley_instapromote.users.id 1 Using where; Using index; Not exists 1 PRIMARY blocked ref actor_id,receiver_id actor_id 17 const,bradley_instapromote.users.id 1 Using where; Using index 2 DEPENDENT SUBQUERY interests index_subquery user_id user_id 8 func 2 Using where 3 DEPENDENT SUBQUERY interests index_subquery user_id,interest interest 77 func 6 Using where
Explain IMG:
