mysql select from multi tables problem

Question

this is the query

SELECT 
                        members.memberID,
                        members.salutation,
                        members.firstName,
                        members.middleName,
                        members.lastName,
                        members.suffix,
                        members.company,

                        addresses.address1,
                        addresses.address2,
                        addresses.city,
                        addresses.state,
                        addresses.postalCode,
                        addresses.country,
                        addresses.memberID,

                        email.email,
                        email.memberID,

                        phonenumbers.phoneNumber, 
                        phonenumbers.memberId,

                        subscriptions.year,
                        subscriptions.memberID
                    FROM 
                        members,
                        addresses,
                        email,
                        phonenumbers,
                        subscriptions
                    WHERE
                        subscriptions.year = '%s'
                            AND
                        subscriptions.memberID = members.memberID
                            AND
                        subscriptions.memberID = addresses.memberID
                            AND
                        subscriptions.memberID = email.memberID
                            AND
                        subscriptions.memberID = phonenumbers.memberID
                    ORDER BY 
                        members.lastName,
                        members.firstName,
                        members.company
LIMIT 0, 10

my problem is its a huge query so Im trying to limit it to so many at a time... its supposed to have over 5000 results... anyway the only limit that works is limit 0, 10 if you do anything else 5, 10 it doesnt work 0, 50 doesnt work... only 0, 10 works...

and when I do 0, 10 the query returns blake firstName, middleName, lastName, and a few others... and when I do a print_r() on the $result it shows them blank as well and there is most def data in the database and there is also no typos for that...

Answer 1

I would like to suggest the use of left outer join and indexing in the fields which you have used for linking both tables and applied where condition. it should increase performanace...

as I got the performance in records of 50,000 rows in main table and multiple child tables with ~10,000 rows...

try left outer join properly and do proper indexing

Edited: with SQL statement:

SELECT 
    subscriptions.year,
    subscriptions.memberID

    members.memberID,
    members.salutation,
    members.firstName,
    members.middleName,
    members.lastName,
    members.suffix,
    members.company,

    addresses.address1,
    addresses.address2,
    addresses.city,
    addresses.state,
    addresses.postalCode,
    addresses.country,
    addresses.memberID,

    email.email,
    email.memberID,

    phonenumbers.phoneNumber, 
    phonenumbers.memberId,

    FROM 
    subscriptions
    left outer join members on subscriptions.memberID = members.memberID
    left outer join addresses on subscriptions.memberID = addresses.memberID
    left outer join email on subscriptions.memberID = email.memberID
    left outer join phonenumbers on subscriptions.memberID = phonenumbers.memberID
    WHERE
    subscriptions.year = '%s'
    ORDER BY 
    members.lastName,
    members.firstName,
    members.company
LIMIT 0, 10

Answer 2

Depending on how many e-mail addresses and phone numbers your members have on average, there might be a problem with your query. Suppose you have the records (most fields omitted for brevity):

members                             addresses             phoneNumbers
memberId | firstName | lastName     memberId | city       memberId | phoneNumber
---------------------------------   -------------------   ----------------------
839      | Joe       | Hallenbeck   839      | New York   839      | 111-11111
                                    839      | L.A.       839      | 222-22222

Then what your join will do is produce not one record for Joe Hallenbeck, but four:

memberId | firstName | lastName    | city     | phoneNumber
-----------------------------------------------------------
839      | Joe       | Hallenbeck  | New York | 111-1111
839      | Joe       | Hallenbeck  | New York | 222-2222
839      | Joe       | Hallenbeck  | L.A.     | 111-1111
839      | Joe       | Hallenbeck  | L.A.     | 222-2222

I don't know if this is what you want, but you should know that this query might produce far more result records than there are members in the database.

For more advice you should post some DDL statements (create table etc.), as Konerak pointed out.