Major Join Optimisation

[jxrd]

Hi all,

Ok...I've recently done a bit of speed-testing on my site, mainly my forum.

 

It turns out, that a join that counts the number of posts for a thread takes about 0.2 seconds...which is crazy. If I take out the join, it takes about 0.05 seconds (but obviously, I can't display how many posts the thread has).

 

I then tried an inner join and it was about 0.09 seconds, but didn't display child forums, as it wouldn't return any posts for them, only threads. I then changed it to use an IF statement. Hard to explain, I'll give you examples:

 

This is the original query:

SELECT F.*, COUNT(P.`ID`) AS `PostCount`
FROM `$tb_Forum` F
LEFT OUTER JOIN `$tb_Forum` P ON (F.`ID`=P.`Thread` OR F.`ID`=P.`ID`) AND P.`Type` IN('thread', 'post')
#blah....and so on

 

And this is the new query:

SELECT F.*, COUNT(P.`ID`) AS `PostCount`
FROM `$tb_Forum` F
INNER JOIN `$tb_Forum` P ON IF(F.`Type`='forum', F.`ID`=P.`Forum` OR F.`ID`=P.`ID`, (F.`ID`=P.`Thread` OR F.`ID`=P.`ID`) AND P.`Type` IN('thread', 'post'))

 

And the new one is about 0.2 seconds faster. Why is this? Are left joins really that slow?

 

I'm just wondering why this is so much faster...and returns the exact same results.

 

Thanks for any info

[fenway]

They can be... especially when you're using fields from the left-joined tables.

 

If you need left join, you have no choice.