We build. You grow.

Get best community software here

Start a social network, a fan-site, an education project with oxwall - free opensource community software

Performance issue? | Forum

Marcus
Marcus Sep 2

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!

The Forum post is edited by Marcus Sep 2
Marcus
Marcus Sep 2

The query above takes 0.5 sec now by using temp table takes 0.002 sec huge improvement why not sure???


CREATE TEMPORARY TABLE tempTable1
    as (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);


select * from tempTable1

The Forum post is edited by Marcus Sep 2
dave Team
dave Sep 7
INNER joins are well known for being very slow.  The temp table is probably just a better use of the resources is all.  That would be my guess.