I have three tables like this:
receipts
±-----------±------±-----±-----------±----------+| customerId | recID | plan | recDate | processed |±-----------±------±-----±-----------±----------+| 9742 | 900 | 1 | 1178726955 | 0 || 2188 | 899 | 1 | 1178670358 | 0 || 2657 | 898 | 1 | 1178639090 | 0 || 9699 | 897 | 1 | 1178627303 | 0 || 1247 | 896 | 1 | 1178570875 | 0 |±-----------±------±-----±-----------±----------+
customer
±------------------------------±---------±----------+| Email | LastName | FirstName |±------------------------------±---------±----------+| user1@domain.com | lname | fname || user2@domain.com | lname | fname || user3@domain.com | lname | fname || user4@domain.com | lname | fname || user5@domain.com | lname | fname |±------------------------------±---------±----------+
user
±---------±-------------±------------+| Approved | UserUpgraded | UpgradeDate |±---------±-------------±------------+| 1 | 1 | 1176134955 || 1 | 1 | 1176078358 || 1 | 1 | 1176047090 || 1 | 1 | 1176035303 || 1 | 1 | 1175978875 |±---------±-------------±------------+
each table is actually much bigger than that, over 10,000 entries … now i need to get all three table’s info at once … since there is no index associated with any of those (poorly designed … not by me) i am forced to use the WHERE clause.
now this is where i get messed up … this query:
SELECT DISTINCT receipts.customerId, receipts.recID, receipts.plan, receipts.recDate, receipts.processed, customer.Email, customer.LastName, customer.FirstNameFROM receipts, customerWHERE receipts.customerId = customer.CustomerIdGROUP BY receipts.customerIdORDER BY receipts.recID DESCLIMIT 5;
produces the following result:
±-----------±------±-----±-----------±----------±------------------------------±---------±----------+| customerId | recID | plan | recDate | processed | Email | LastName | FirstName |±-----------±------±-----±-----------±----------±------------------------------±---------±----------+| 9742 | 900 | 1 | 1178726955 | 0 | user1@domain.com | lname | fname || 2188 | 899 | 1 | 1178670358 | 0 | user2@domain.com | lname | fname || 2657 | 898 | 1 | 1178639090 | 0 | user3@domain.com | lname | fname || 9699 | 897 | 1 | 1178627303 | 0 | user4@domain.com | lname | fname || 1247 | 896 | 1 | 1178570875 | 0 | user5@domain.com | lname | fname |±-----------±------±-----±-----------±----------±------------------------------±---------±----------+5 rows in set (0.01 sec)
which is fine … but as soon as i add my third and last table into play … the query takes about 2 mins to process and uses 50+% CPU … this is the query i try to run:
SELECT DISTINCT receipts.customerId, receipts.recID, receipts.plan, receipts.recDate, receipts.processed, customer.Email, customer.LastName, customer.FirstName, user.Approved, user.UserUpgraded, user.UpgradeDate FROM receipts, customer, user WHERE receipts.customerId=customer.CustomerId AND customer.Email=user.UserEmail GROUP BY receipts.customerId ORDER BY receipts.recID DESC;LIMIT 5;
am i doing something wrong ? any help would be appreciated
p.s. the reason im doing distinct and group by customer id from the receipts table is because there are multiple receipts for each customer … and i want to get the latest one!
p.s.s. the user table also has an email column, thats how the user and customer tables are linked …