How do I limit this MySQL query

Posted on 2011-04-25
Medium Priority
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

Question by:ImagineItDigital
LVL 111

Expert Comment

by:Ray Paseur
ID: 35462883
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.

Expert Comment

ID: 35467241
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 Comment

ID: 35471439
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.  
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.


Expert Comment

ID: 35472451
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 Comment

ID: 35477852
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.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35480228
what about this?

note that a index on registrations.client_id (+ optionally, on the same index, the paid field) is a must to get this query working fine ...
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 
, ( SELECT SUM(r.paid) FROM registrations r WHERE r.client_ID = newcampers.client_id ) paid_amount
FROM clients AS newcampers 
LEFT JOIN clients AS referringcampers 
  ON (newcampers.referred_by=referringcampers.email)  
WHERE newcampers.referred_by != '' 
 AND newcampers.referral_paid != 1 
 AND 149<= ( SELECT SUM(r.paid) FROM registrations r WHERE r.client_ID = newcampers.client_id )
ORDER BY newcampers.lastname

Open in new window


Author Closing Comment

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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question