Jump to content


Photo

sql statement with groups and joins


  • Please log in to reply
12 replies to this topic

#1 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 22 February 2006 - 03:56 PM

I think my page is timing out cause I wrote the sql statment in a poor way .. everything worked fine until I wanted to put a date range on the clicks.

.. basically want I want to do is get the "clicks" that are less than a month old ..

count(distinct concat( c.user_id, c.ip_address)) AS clicks,

is there a way to put the where statement as part of the count?
because when I put it a the bottom .. I think it's doing a lot more work than I intend

$sql .="&& str_date >DATE_SUB(curdate(), INTERVAL 1 MONTH )";

here is the live version
[a href=\"http://www.illustrationmundo.com/illustrators2.php\" target=\"_blank\"]http://www.illustrationmundo.com/illustrators2.php[/a]

and here is the code



$sql = '
    
     SELECT
a.*,
round(AVG(b.score),1) AS avg,
count(distinct concat( b.user_id,  b.judge)) AS total_votes,
count(distinct concat( c.user_id,  c.ip_address)) AS clicks,
d.*
FROM (illustrators_table a
LEFT JOIN score_table b ON ( a.user_id = b.user_id )
LEFT JOIN external_url c ON ( a.user_id = c.user_id )
LEFT JOIN users d ON ( a.user_id = d.user_id ))';

    $sql = $sql." ".$q_visible_temp;
    $sql = $sql." ".$q_s_verified_temp;
    $sql = $sql." ".$q_i_search_temp;
    $sql = $sql." ".$q_medium_temp;
    $sql = $sql." ".$q_style_temp;
    $sql = $sql." ".$q_category_temp;
    $sql = $sql." ".$q_feature_temp;
    $sql = $sql." ".$q_score_temp;
    $sql .="&& str_date >DATE_SUB(curdate(), INTERVAL 1 MONTH )";
    $sql= $sql. " GROUP BY c.user_id "; 
    $sql = $sql." ".$str_date_temp;


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 February 2006 - 07:53 PM

That makes it look like you're putting it after the GROUP BY -- could you post the final, concatenated query?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 22 February 2006 - 07:59 PM

[!--quoteo(post=348452:date=Feb 22 2006, 04:53 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 22 2006, 04:53 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
That makes it look like you're putting it after the GROUP BY -- could you post the final, concatenated query?
[/quote]

sure

SELECT a.*, 
round(AVG(b.score),1) AS avg, 
count(distinct concat( b.user_id, b.judge)) AS total_votes, 
count(distinct concat( c.user_id, c.ip_address)) AS clicks, 
d.* FROM (illustrators_table a 
LEFT JOIN score_table b ON ( a.user_id = b.user_id ) 
LEFT JOIN external_url c ON ( a.user_id = c.user_id ) 
LEFT JOIN users d ON ( a.user_id = d.user_id )) 
WHERE visible ="t" 
&& s_verified ="t" 
&& str_date >DATE_SUB(curdate(), INTERVAL 1 MONTH ) 
GROUP BY c.user_id 
ORDER BY a.user_id DESC


#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 22 February 2006 - 10:01 PM

I notice that you named it str_date, are you storing it as a string type (varchar, tinytext, etc), or a date type (DATETIME, TIMESTAMP, etc)?

If you're storing it as a string, the query as written will not be able to use the index on str_date. It will want to convert str_date to a date type and then compare it with the DATE_SUB.

To restore indexing, you need to force it to stringify the DATE_SUB into a format that matches your str_date format. My example assumes your format is YYYYMMDD.

Also, the CONCAT functions appear to be superfluous and could slow things down a little. I removed them.
SELECT a.*,
round(AVG(b.score),1) AS avg,
count(distinct b.judge) AS total_votes,
count(distinct c.ip_address) AS clicks,
d.* FROM illustrators_table a
LEFT JOIN score_table b ON a.user_id = b.user_id
LEFT JOIN external_url c ON a.user_id = c.user_id
LEFT JOIN users d ON a.user_id = d.user_id 
WHERE visible ="t"
&& s_verified ="t"
&& str_date > CAST(DATE_SUB(curdate(), INTERVAL 1 MONTH )+0 as CHAR)
GROUP BY c.user_id
ORDER BY a.user_id DESC


#5 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 22 February 2006 - 10:12 PM

hey thanks wickning1 .. my naming conventions are bad .. so it was kind of misleading .. the date type is actually stored as datetime so no need to convert ..

is there anyway to put the condition up on this part
count(distinct concat( b.user_id, b.judge)) AS total_votes where str_date >DATE_SUB(curdate(), INTERVAL 1 MONTH )

thanks n8w



#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 February 2006 - 10:13 PM

Intersting approach... but doing a CAST() for each potential record is also a perfomance hit. The real question is why the date is being stored as anything but a date to begin with.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 22 February 2006 - 10:19 PM

[!--quoteo(post=348509:date=Feb 22 2006, 07:13 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 22 2006, 07:13 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Intersting approach... but doing a CAST() for each potential record is also a perfomance hit. The real question is why the date is being stored as anything but a date to begin with.
[/quote]


the date is stored as datetime .. the name str_date is misleading .. I must admit

I don't get the data I want .. but it doesnt hang when I do this

note the "<"

no hang
&& str_date < DATE_SUB(curdate(), INTERVAL 1 MONTH )

correct but hangs
&& str_date > DATE_SUB(curdate(), INTERVAL 1 MONTH )


[a href=\"http://www.illustrationmundo.com/illustrators3.php?str_date=bc\" target=\"_blank\"]http://www.illustrationmundo.com/illustrat...php?str_date=bc[/a]


#8 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 22 February 2006 - 10:36 PM

What you're looking at doing actually involves a few GROUPings, so it should be subquery based. Also, you're going to need independent dates for both clicks and votes, or else one of them is going to be counted back forever.

If you're doing what I think you're doing, it should end up looking something like this.

SELECT a.*, b.avg_score, b.total_votes, c.clicks, d.*
FROM illustrators_table a
LEFT JOIN users d ON a.user_id = d.user_id
LEFT JOIN (
    SELECT user_id, AVG(score) as avg_score, COUNT(*) as total_votes FROM score_table WHERE score_date > curdate() - INTERVAL 1 MONTH GROUP BY user_id
) b ON a.user_id=b.user_id
LEFT JOIN (
    SELECT user_id, COUNT(*) as clicks FROM external_url  WHERE click_date > curdate() - INTERVAL 1 MONTH GROUP BY user_id
) c ON a.user_id=c.user_id
WHERE visible="t" AND s_verified="t"
ORDER BY a.user_id DESC

fenway - the CAST is only done once. DATE_SUB(curdate(), INTERVAL 1 MONTH) is a constant. Once it gets calculated, it can be used to do an index lookup.

#9 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 22 February 2006 - 11:50 PM

sooooooooooo cool .. I didn't even know you could run a select within a join ..

the performance is defiinitely better .. but it still looks like it's selecting all the clicks

do you see anything strange in my sql statement

[a href=\"http://www.illustrationmundo.com/illustrators3.php?str_date=bc\" target=\"_blank\"]http://www.illustrationmundo.com/illustrat...php?str_date=bc[/a]


#10 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 23 February 2006 - 12:11 AM

It looks like you're selecting b.* twice, which is unnecessary, but other than that it looks good.

#11 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 23 February 2006 - 12:23 AM

thank you sooooooooooooo much .. this works perfectly now and much faster .. you just opened the door to a whole new world .. thanks!

#12 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 23 February 2006 - 03:55 AM

Always nice to help someone who's stumped but at least has a clue. So many posters just want you to teach them programming 101, especially in the PHP Help forum.

The site looks pretty nice, good luck with it.

#13 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 February 2006 - 09:43 AM

[!--quoteo(post=348519:date=Feb 22 2006, 05:36 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 22 2006, 05:36 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
fenway - the CAST is only done once. DATE_SUB(curdate(), INTERVAL 1 MONTH) is a constant. Once it gets calculated, it can be used to do an index lookup.
[/quote]
Indeed -- you have an attention that I obviously lack. I don't think I've ever passed a constant into DATE_SUB(), so I automatically assumed it was the field value -- which naturally would be rather slow. Excellent solution, by the way.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users