Jump to content

Count() with a subQuery seems to be not optimized...


arianhojat

Recommended Posts

Hello I was making a Query earlier and it seemed basically I was repeating myself and being inefficient so i was wondering if anyone ran into the same issues and has a solution...


The Query looks long like...
SELECT  *
FROM
111
join 222
join 333
join 444
join 555
join 666
join 777

WHERE
LLLLL
OOOO
NNNN
GGGG
LLLLL
OOOO
NNNN
GGGG
LLLLL
OOOO
NNNN
GGGG

my Query returns something like
IndivClassID(primary key)    MasterClassID ... ... ...
100 66 ... ... ...
101 66 ... ... ...
201 76 ... ... ...
202 88 ... ... ...
202 88 ... ... ...
202 88 ... ... ...



I would like to return Count(MasterClassID) in that Query, so should look like...
100 66 2 ... ... ...
101 66 2 ... ... ...
201 76 1 ... ... ...
202 88 3 ... ... ...
202 88 3 ... ... ...
202 88 3 ... ... ...

So my 1st thought is usual Subquery. but it seems very ghetto/repetitive to do this... If I do a GROUP BY MasterClassID, of course, I lose info so i guess I can't do that.

The ghetto way i came up with which works, but probably uses double the time is...
SELECT  *,
(
SELECT  Count(MasterClassID)
FROM
111_temp
join 222_temp
join 333_temp
join 444_temp
join 555_temp
join 666_temp
join 777_temp

WHERE
LLLLL
OOOO
NNNN
GGGG
LLLLL
OOOO
NNNN
GGGG
LLLLL
OOOO
NNNN
GGGG

AND 111_temp.MasterClassID=111.MasterClassID /* returns count for this 1 row */
GROUP BY MasterClassID
) as Count
FROM
111
join 222
join 333
join 444
join 555
join 666
join 777

WHERE
LLLLL
OOOO
NNNN
GGGG
LLLLL
OOOO
NNNN
GGGG
LLLLL
OOOO
NNNN
GGGG
Link to comment
Share on other sites

Well, depending on what you're using it for, you have a few options.  You can issue a second query and just get back the counts.  You can forgoe the UIDs of the things your grouping, if you don't need them, or GROUP_CONCAT(), or whatever.  You can run a correlated subquery on whatever table contains MasterClassID and do a much simpler count, probably.
Link to comment
Share on other sites

only thing about GROUP_CONCAT is i would get result like...

'100,101'        66 2
'201'              76 1
'202,202,202' 88 3

so it would have the string of the Individual ID's which are related to their respected master ID, and then the count of the masterIDs for that group.

but the other data in 'Individual ' table is lost since it is GROUPED BY MasterClassID.


i guess ill stick with longgg subselect or a 2nd query in the php loop.

antother option maybe is...

4. Use a php database cursor (aka use mysql_data_seek function) to keep looking into the future, incrementing a count variable until new MasterClassID reached, then put cursor back at row 'i' that it is processing and it will have the count information. eh as well but maybe 7-10 lines of extra code. but then again the subselect is the same... any other suggestions, i am open to.

thanks, fenway.


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.