Ineffecient query - Is there a better way?

[Bagger Vance]

I'm trying to determine how many distinct "members" have paid in either one of
two tables. In the table 'confirm_tourns', the field 'tourn_paid' will = 1 for
paid. In the table 'confirm_lessons', the field 'lesson_paid' will = 1 for
paid. I'm using the member_id from a members tables, where each member_id is
unique.

Here are the two pieces of code that I have tried. Both either take forever
and nearly grind my computer to a halt, or else timeout. I'm thinking there
must be a more efficient way to write this, but am not an expert in MySQL.

Thanks

<--- FIRST ATTEMPT --->
SELECT COUNT(DISTINCT members.member_id ) AS total_members
FROM members, confirm_lessons, confirm_tourns
WHERE (members.member_id = confirm_lessons.member_id AND lesson_paid =1)
OR (members.member_id = confirm_tourns.member_id AND tourn_paid =1)

<--- SECOND ATTEMPT --->


<cfquery name="get_paid_members" datasource="dbtcjga" username="tcjga"
password="uin2tcjga">
SELECT member_id
FROM members
WHERE app_status="confirmed"
</cfquery>

<cfset partIcipating_members=0>

<cfoutput query="get_paid_members">
<cfquery name="count_part_members" datasource="dbtcjga" username="tcjga"
password="uin2tcjga">
SELECT COUNT(DISTINCT members.member_id ) AS part_members
FROM members, confirm_lessons, confirm_tourns
WHERE members.member_id=#member_id#
AND(members.member_id = confirm_lessons.member_id AND
lesson_paid =1)
OR (members.member_id = confirm_tourns.member_id AND
tourn_paid =1)
</cfquery>

<cfif count_paid_members.part_member GT 0>
<cfset participating_members = participating_members + 1>
</cfif>

</cfoutput>

<cfoutput query="count_part_members">
#part_members#</cfoutput>

[Kronin555]

the first way is the way to do it, but you can clean it up a bit. Also, you
need to make sure you have indexes on the 3 tables as follows:
Unique index on Members.member_id
Index on confirm_lessons.member_id
Index on confirm_tourns.member_id

SELECT COUNT(members.member_id) AS total_members
FROM members LEFT JOIN confirm_lessons ON (members.member_id =
confirm_lessons.member_id)
LEFT JOIN confirm_tourns ON (members.member_id = confirm_tourns.member_id)
WHERE lesson_paid = 1 and tourn_paid = 1
GROUP BY members.member_id

[Dan Bracuk]

Always more than one way to do something.

select distinct member_id
from confirm_lessons
union
select distinct member_id
from confirm_tourns

Your answer will be the recordcount.

If your db supports subqueries in the where clause, do this
select count(member_id) as myanswer
from
(select distinct member_id
from confirm_lessons
union
select distinct member_id
from confirm_tourns ) x

[Bagger Vance]

Sorry to be so tardy in my response, but I got pulled away by other projects.

I don't know if I understand the indexes. Members.member_id is the primary
key, so I think that means it already has a unique index. I added indexes for
the other two that you mentioned, but didn't select unique or full text which
seemed to be the two options available. Hopefully that is correct.

When I ran your query, it returned multiple rows of "total_members". Perhaps
if they were totalled they would be the correct number?

I appreciate your response. Any additional help would be welcome.

Thanks


Originally posted by: Kronin555
the first way is the way to do it, but you can clean it up a bit. Also, you
need to make sure you have indexes on the 3 tables as follows:
Unique index on Members.member_id
Index on confirm_lessons.member_id
Index on confirm_tourns.member_id

SELECT COUNT(members.member_id) AS total_members
FROM members LEFT JOIN confirm_lessons ON (members.member_id =
confirm_lessons.member_id)
LEFT JOIN confirm_tourns ON (members.member_id = confirm_tourns.member_id)
WHERE lesson_paid = 1 and tourn_paid = 1
GROUP BY members.member_id

[Bagger Vance]

Thanks for your suggestion.

The result from your query comes up with a list of member_id's, not a record
count as far as I can tell. I tried your second query, but it produced an
error. I am using mySQL.

Bagger




\Originally posted by: Dan Bracuk
Always more than one way to do something.

select distinct member_id
from confirm_lessons
where lessons_paid = 1
union
select distinct member_id
from confirm_tourns
where tourns_paid = 1
Your answer will be the recordcount.

If your db supports subqueries in the where clause, do this
select count(member_id) as myanswer
from
(select distinct member_id
from confirm_lessons
where lessons_paid = 1
union
select distinct member_id
from confirm_tourns
where tourn_paid = 1) x

[Dan Bracuk]

Originally posted by: Bagger Vance
Thanks for your suggestion.

The result from your query comes up with a list of member_id's, not a record
count as far as I can tell. I tried your second query, but it produced an
error. I am using mySQL.

Bagger


Recordcount is one of three variables that are available with cfquery. The
other two are currentrow and columnlist. Details are in the cfml reference
manual. If you don't have one, the internet does.

By the way, you had the answer.

[MikerRoo]

When posing problems, please mention what version of MySQL you are using --
your best options vary wildly as MySQL adds features.

Anyway, Kronin's query looked close. Try this:

SELECT COUNT (members.member_id) AS total_members
FROM members
LEFT JOIN confirm_lessons ON (members.member_id = confirm_lessons.member_id)
LEFT JOIN confirm_tourns ON (members.member_id = confirm_tourns.member_id)
WHERE lesson_paid = 1
OR tourn_paid = 1

[Bagger Vance]

Ah ha! Thanks for the explanation Dan. The query does work significantly faster
than what I was using. The only odd thing is that my 1st Attempt query gives a
total_members count of one less than the recordcount of yours. There is
probably another simple explanation for this, but I'm not sure what it is.

Thanks again for your help. -Bagger

Originally posted by: Dan Bracuk
Originally posted by: Bagger Vance
Thanks for your suggestion.

The result from your query comes up with a list of member_id's, not a record
count as far as I can tell. I tried your second query, but it produced an
error. I am using mySQL.

Bagger


Recordcount is one of three variables that are available with cfquery. The
other two are currentrow and columnlist. Details are in the cfml reference
manual. If you don't have one, the internet does.

By the way, you had the answer.

significantly

[Bagger Vance]

Something must be off on this one... This result of total_members from the
query below was 1982 when it should be 427.

Thanks for your help. This is a great learning experience seeing the different
approaches to the same problem.

Originally posted by: MikerRoo
When posing problems, please mention what version of MySQL you are using --
your best options vary wildly as MySQL adds features.

Anyway, Kronin's query looked close. Try this:

SELECT COUNT (members.member_id) AS total_members
FROM members
LEFT JOIN confirm_lessons ON (members.member_id = confirm_lessons.member_id)
LEFT JOIN confirm_tourns ON (members.member_id = confirm_tourns.member_id)
WHERE lesson_paid = 1
OR tourn_paid = 1

[Kronin555]

Bagger,

Sorry about the faulty query. I shouldn't have had the GROUP BY in there.
Also, I was using AND, where there should have been an OR. Nice corrections,
MikerRoo.

The reason you're getting more than you expect is because each member_id is
being counted for each lesson or tourn they've paid for.

Try adding a DISTINCT into your COUNT, like so:

SELECT COUNT (DISTINCT(members.member_id)) AS total_members
FROM members
LEFT JOIN confirm_lessons ON (members.member_id = confirm_lessons.member_id)
LEFT JOIN confirm_tourns ON (members.member_id = confirm_tourns.member_id)
WHERE lesson_paid = 1
OR tourn_paid = 1