There has to be a better way to do this

Hey guys, new account cause I couldn’t remember my old password, sigh…

Ok here is my WORKING query.

It gets all my items from the fridigy table, then counts the number of comments on that item from comments table, then does the same for the positive votes, and then the negative votes, both of which live in the pulse_votes tables.


"SELECT * ,(
          SELECT COUNT(*) 
          FROM comments C
          WHERE C.oid=F.id
        ) AS CommentCount,(
          SELECT COUNT(*) 
          FROM pulse_votes P
          WHERE P.item_id=F.id AND P.vote_value=1
        ) AS upCount,(
          SELECT COUNT(*) 
          FROM pulse_votes P 
          WHERE P.item_id=F.id AND P.vote_value=-1
        ) AS downCount  
        FROM fridigies F
        INNER JOIN users U ON U.user_id = F.uid
        WHERE type='{$category}' ORDER BY F.date_added"; 

This query works fine, in that it gets me counts for the 3 things I need for the output, “Number of Comments”, “Number of positive votes”, number of negative votes"

But there has to be a better way of doing this? I can only assume this query is quite slow (no idea how to test)

Any help is appreciated :slight_smile:

really? no idea?

copy your sql code out of php, substitute actual values for the php variables ({$category}), and run the result directly in mysql, either via a front end app like the mysql workbench, or via the mysql command line

Yeah but that is running on my localhost which is super fast, so I’m not seeing much difference between this query and one that doesn’t have inner selects.

What I meant when I mentioned testing was more to do with some way of running the query multiple times concurrently which I assume would give me a better result when it came to comparing with simpler queries.

I’m a javascript dev by trade, mySql is just something I do occasionally, hence the asking if there is a better way of accomplishing what I’m doing?

edit***

Query above
Showing rows 0 - 10 (11 total, Query took 0.0209 sec)

Query with no inner selects
Showing rows 0 - 10 (11 total, Query took 0.0198 sec)

Not much difference?

for a return of 11 rows, you likely had in-memory execution

do an EXPLAIN on the query on the live server

No live server yet, and only some small sample data while I finish this project off.

Anyway, any thoughts on the query, is it ok do do it that way, not ok, examples of better queries that do the same thing?

:slight_smile:

alternaive query:

SELECT f.*
     , COALESCE(c.cnt,0) AS CommentCount
     , COALESCE(v.ucnt,0) AS upCount
     , COALESCE(v.dcnt,0) AS downCount
  FROM fridigies AS f
INNER 
  JOIN users AS u 
    ON u.user_id = f.uid
LEFT OUTER
  JOIN ( SELECT oid
              , COUNT(*) AS cnt
           FROM comments 
         GROUP
             BY oid ) AS c
    ON c.oid = f.id
LEFT OUTER
  JOIN ( SELECT item_id
              , COUNT(CASE WHEN P.vote_value = 1
                           THEN 'u' END) AS Ucnt 
              , COUNT(CASE WHEN P.vote_value = -1
                           THEN 'd' END) AS Dcnt 
           FROM pulse_votes 
         GROUP
             BY item_id ) AS v
    ON v.item_id = f.id
 WHERE f.type = '{$category}' 
ORDER 
    BY f.date_added

Wow that is epic, I like the way it only uses 3 selects instead of 4, so that should be a bit nicer to the database instead of my backhand to the face, of a query.

Thanks for the help, there are a couple things I don’t quite understand about your query, but should make for some interesting reading today, points me a good direction anyway :slight_smile:

just ask if you’re stumped

:slight_smile:

Yep will do, just so I have it right.

The ‘COALESCE(c.cnt,0)’ is a fail safe, if c.cnt is null it will return 0 instead. Correct?

The “CASE WHEN P.vote_value = 1 THEN ‘u’ END” - I kind of understand this, never used CASE query before, so bare with me.

My understanding is this, if ‘P.vote_value = 1’ THEN (do something) END, Am I correctly assuming here that because we are grouping this sub query for pulse_votes by ‘item_id’ at the end, that the THEN statement of ‘u’ simply returns a row, we don’t need to specify explicitly a value like u.id, because eventually we group by item_id?

I spose the simplicity of THEN ‘u’ throws me off a little :slight_smile:

Thanks again, this pattern certainly has improved my knowledge of MySql A little bit more :slight_smile:

correct, and the null is possible since we used a LEFT OUTER JOIN (there might be no comments for a given id)

bear with me = have patience
bare with me = let’s get undressed together :smiley:

close – it’s not actually “do something” (that’s a programming midset, eh), rather, it’s “use this value”

i will very often use ‘humpty’ and ‘dumpty’ or ‘curly’, ‘larry’, and ‘moe’ as the values – in your case i used ‘u’ and ‘d’

the point is, they could have been ~any~ values at all

what’s happening is that the CASE expression is inside a COUNT aggregate function, so what it does is count all the values

now, when the WHEN condition is false, the CASE expression looks for the ELSE value, and the part you might not have been aware of is that if the ELSE value isn’t explicitly given, it defaults to NULL

and that’s the crux of the technique – NULLs are skipped, i.e. not included, in any aggregate function like COUNT or SUM or MAX

so the net effect is that each COUNT function counts only those non-null values provided by the THEN part of the CASE – and that value could just as easily have been anything

:slight_smile:

Hahahahaha, I might be writing MySql instead of JavaScript but I still don’t want to get undressed together, unless of course you’re a ‘hot chick’, which is impossible cause there are no girls on the Internet.

:lol: