Jump to content

sql statement with groups and joins


n8w

Recommended Posts

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



[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;
[/code]
Link to comment
Share on other sites

[!--quoteo(post=348452:date=Feb 22 2006, 04:53 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 22 2006, 04:53 PM) [snapback]348452[/snapback][/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

[code]
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[/code]
Link to comment
Share on other sites

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.
[code]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[/code]
Link to comment
Share on other sites

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

Link to comment
Share on other sites

[!--quoteo(post=348509:date=Feb 22 2006, 07:13 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 22 2006, 07:13 PM) [snapback]348509[/snapback][/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]
Link to comment
Share on other sites

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.

[code]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[/code]

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

[!--quoteo(post=348519:date=Feb 22 2006, 05:36 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 22 2006, 05:36 PM) [snapback]348519[/snapback][/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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.