MySql gurus... is this query ok (it works) top referrer

[grant.hayman]

I am using the addon that comes with vbadvanced for top poster to show top referrer for this month instead.

I have wrote this query to get the data that I need and it seems to work fine but was wondering if any sql gurus could check it over and see if it is the best way to do it as I am new to sql join queries (new to MySql in genral :) ).

SELECT ur.username, COUNT( user.referrerid ) AS totalreferred
FROM user AS ur
RIGHT JOIN user ON ur.userid = user.referrerid
WHERE user.referrerid != ''
AND MONTH( FROM_UNIXTIME( user.joindate ) ) = MONTH( now( ) )
GROUP BY user.referrerid
ORDER BY totalreferred DESC

Thanks

Grant

[sarahk]

I'd add a check for the year as well, although I'd probably feed the current month and year into the sql rather than get MySQL to work it out.

From my experience with other databases I would expect MySQL to be working out the month(now()) and year(now()) for every row which is unnecessary processing. Far better to do it once in PHP and have it as a constant in the query.

AND YEAR( FROM_UNIXTIME( user.joindate ) ) = YEAR( now( ) )

[grant.hayman]

Thanks Sarahk,

That makes a lot of sense, I will add that to the php and thanks for the code (saves me googling :D ).

[Farcaster]

I noticed that you are also doing a RIGHT join and then excluding anything that is blank in your where clause. A right join is typically more expensive than an inner join, so I would rewrite it to be:

INNER JOIN user ON ur.userid = user.referrerid

And remove the:

WHERE user.referrerid != ''

as the INNER JOIN will implicitly eliminate blanks in the join.

[grant.hayman]

Thanks Farcaster,

I replaced the RIGHT JOIN with INNER JOIN and it does indeed produce the same result which confused me because i thought that would bring back results from both tables which where identical i didnt relised it ignored the blank results (still learning, cheers).

the results of the query will be held as an array and outputted to a module for display.

I would love to add a position to the array and the only way i could see to do this was to add a positions filed on the fly so position is held in the array do you know of any way of doing this... I'm guessing this is not possible but was wondering if you knew a way of doing this without a complicated subselect or temp table which im sure would slow everything down.

Any thoughts would be appreciated.

Grant

--------------- Added 1200331410 at 1200331410 ---------------

Sorted the position thing... I was limiting myself to try to use a script i had ... (not the best way to do it lol) I just rewrote the script.