asked on

How do I limit this MySQL query

I'm trying to figure out how to limit this query by a field in another table (registrations). The field is called 'paid'. I need to only return a row if the new camper has paid more than $149. The attached code is working fine, but does not limit the query by this field in the registrations table. The connecting key between the tables (clients and registrations) is client_id.

My feeble attempts at this are resulting in an error of "The SELECT would examine more than MAX_JOIN_SIZE rows". Have I given all the information needed to solve this little puzzle?

Thanks in advance to anyone who helps out.

SELECT newcampers.referred_by AS referred_by, newcampers.client_id AS new_id, newcampers.firstname AS new_first, newcampers.lastname AS new_last, referringcampers.client_id AS referring_id, referringcampers.firstname AS referred_first, referringcampers.lastname AS referred_last FROM clients AS newcampers LEFT JOIN clients AS referringcampers ON (newcampers.referred_by=referringcampers.email)  WHERE newcampers.referred_by != '' AND newcampers.referral_paid != 1 ORDER BY newcampers.lastname

Open in new window

Ray Paseur

Why not do it the easy way with two (or more) queries. If you're only talking about a few thousand campers it won't take too long.

Step 1: SELECT client_id FROM registrations WHERE paid > 149
Step 2: Create a set of client_id numbers in a WHERE clause like "3 OR 17 OR 18 OR etc..."
Step 3. Use the created WHERE clause to SELECT from the table that has the data you want to retrieve.

msk_apk

clients and registrations table are not joined by client id, i believe. they are joined by (newcampers.referred_by=referringcampers.email). Normally number field joins are far better than string fields. If indices are added for joining filelds, performance would be much better. if you would like to reduce the number of rows to be examined, then adding index to join field and where criteria field would help.

ImagineItDigital

ASKER

No, clients and registrations are joined by a numeric index of client_id. The above MySQL query doesn't address the registrations table at all yet (though I need it to). It is currently comparing one field of the table clients to another field in that same table.

The clients table has a field named "referred_by", which is being compared to the email_address field in that same table to pull client information who have been referred by other clients.

msk_apk

sorry for this. however my answer holds good to reduce the number of rows to be joined.
to reduce the number of rows to be joined,
1. add indices on join columns and columns on which where criteria is applied.
2. do denormalization and resign the tables.

if the number of rows joined are really huge, then try increasing SET SQL_MAX_JOIN_SIZE or SET SQL_BIG_SELECTS=1

ImagineItDigital

ASKER

Thanks for your help and time. I was really hoping I could get an actual MySQL revision. My MySQL skills are limited when it comes to complex joins, etc. There's something I'm missing here. FYI - there are about 1,200 rows in the client table, and about 2,000 rows in the registrations table.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ImagineItDigital

ASKER

Thanks! This did exactly what I was looking for. I appreciate it greatly!