row count query

[toenailsin]

i'm having trouble getting a count query of a reasonable speed.

i need a count of all clients relating to a member, bearing in mind that a client might be related to a member in any number of ways.

with all conditions in one query, it takes 27 seconds (testing with 150k rows). individual querys i can get it to 1 second, but that doesn't factor in duplicates

basic table layout (omitted groups, clients and members tables. they're unimportant):
client2member
-member_id
-client_id

client2group
-group_id
-client_id

tasks
-member_id
-client_id

[NogDog]

My first thought is to make a stored procedure that will create a temporary table, then insert client IDs into it from each relevant table based on the applicable member/group value. Then the procedure would do a count() of a SELECT DISTINCT on that temporary table.

[toenailsin]

didn't think of that. i'll give it a go

[toenailsin]

wicked. its workin. 0.8 seconds for 150k rows ^.^

[NogDog]

Cool. I wasn't entirely sure it would be that efficient, but I figured it had a good chance of running a lot faster than 27 seconds. :)