Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 10 Jan 2008, 09:54
grant.hayman grant.hayman is offline
 
Join Date: Feb 2007
MySql gurus... is this query ok (it works) top referrer

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 ).


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Thanks

Grant
Reply With Quote
  #2  
Old 10 Jan 2008, 17:19
sarahk's Avatar
sarahk sarahk is offline
 
Join Date: Jun 2004
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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

__________________
Property Management Services
Simple SEO Blog
Reply With Quote
  #3  
Old 11 Jan 2008, 07:30
grant.hayman grant.hayman is offline
 
Join Date: Feb 2007
Thanks Sarahk,

That makes a lot of sense, I will add that to the php and thanks for the code (saves me googling ).
Reply With Quote
  #4  
Old 11 Jan 2008, 21:47
Farcaster Farcaster is offline
 
Join Date: Dec 2005
Real name: Robert Howard
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.
Reply With Quote
  #5  
Old 14 Jan 2008, 13:41
grant.hayman grant.hayman is offline
 
Join Date: Feb 2007
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 14 Jan 2008 at 16:23 ---------------

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.

Last edited by grant.hayman; 14 Jan 2008 at 16:23. Reason: Auto-Merged DoublePost
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 02:21.

Layout Options | Width: Wide Color: