Its been quite some time since I tried to find a way to speed this query up which slows db big time and if u combine it with group by then just grab a chair kick back and maybe even grab a soda or something case its gonna take ages lol.
INNER JOIN `ow_base_user` base_user_table_alias ON base_user_table_alias.`id` = someId
LEFT JOIN `ow_base_user_suspend` base_user_suspend_table_alias ON base_user_suspend_table_alias.`userId` = `base_user_table_alias`.`id`
WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1)
I have tried adding index to both of them but no joy!
I try to avoid this query as much as I can but its so essential you just gotta use it!
Any inputs on this issue will be hugely appreciated.
Here i try to organize users by friends count and OMG its slow!
SELECT f.`userId`,count(f.`userId`)as score
FROM `ow_friends_friendship` f
INNER JOIN `ow_base_user` base_user_table_alias ON base_user_table_alias.`id` = f.`friendId`
LEFT JOIN `ow_base_user_suspend` base_user_suspend_table_alias ON base_user_suspend_table_alias.`userId` = `base_user_table_alias`.`id`
WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) and f.`status` = 'active'
GROUP BY f.userId ORDER BY count(f.`userId`) DESC
Now u get rid of all those inner and left joins its quite fast!
