How do I limit this MySQL query

Posted on 2011-04-25
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 (  WHERE newcampers.referred_by != '' AND newcampers.referral_paid != 1 ORDER BY newcampers.lastname

Open in new window

Question by:ImagineItDigital
    LVL 107

    Expert Comment

    by: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.
    LVL 4

    Expert Comment

    clients and registrations table are not joined by client id, i believe. they are joined by ( 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

    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.  
    LVL 4

    Expert Comment

    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

    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 142

    Accepted Solution

    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 (  
    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

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    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.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now