Mysql select from multi tables problem

Question

this is a request

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 a huge query, so I'm trying to limit it so many at a time ... it is supposed to have more than 5000 results ... anyway, the only limit that works is the 0.10 limit if you do something else 5, 10 it does not work 0, 50 does not work ... only 0, 10 works ...

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

Answer 1

I would suggest using left outer join and indexing in the fields that you used to link both tables and applied where this condition is. he must increase performanace ...

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

try the right left outer join and do the right 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 email addresses and phone numbers users have on average, there may be a problem with your request. Suppose you have entries (most fields are omitted for brevity):

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

Then your connection will produce not one entry 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 | LA | 111-1111 839 | Joe | Hallenbeck | LA | 222-2222 

I don’t know if this is what you want, but you should know that this query can lead to much more result records than in the database.

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