We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How do I limit this MySQL query

ImagineItDigital
on
Medium Priority
296 Views
Last Modified: 2012-05-11
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

Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
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.

Commented:
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.

Author

Commented:
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.  

Commented:
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

Author

Commented:
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.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks!  This did exactly what I was looking for.  I appreciate it greatly!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.