sql statement with groups and joins

[n8w]

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]

[fenway]

That makes it look like you're putting it after the GROUP BY -- could you post the final, concatenated query?

[n8w]

[!--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]

[wickning1]

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]

[n8w]

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

[fenway]

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.

[n8w]

[!--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]

[wickning1]

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.

[n8w]

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]

[wickning1]

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

[n8w]

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

[wickning1]

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.

[fenway]

[!--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.