Jump to content


Photo

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


  • Please log in to reply
3 replies to this topic

#1 arianhojat

arianhojat
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 12 September 2006 - 07:07 PM

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

#2 fenway

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

Posted 12 September 2006 - 07:32 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 arianhojat

arianhojat
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 12 September 2006 - 08:57 PM

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.




#4 fenway

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

Posted 12 September 2006 - 09:06 PM

Not that you couldn't count them yourself in PHP...
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