Poorly Written Query or Crappy Performance?

[zeep25]

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

 

this query:

 

SELECT  
DISTINCT receipts.customerId, 
receipts.recID, 
receipts.plan, 
receipts.recDate, 
receipts.processed, 
customer.Email, 
customer.LastName, 
customer.FirstName
FROM 
`receipts`, 
`customer`
WHERE 
receipts.customerId = customer.CustomerId
GROUP BY 
receipts.customerId
ORDER BY 
receipts.recID DESC
LIMIT 
5;

works perfectly and 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)

 

now this is where i get messed up ...  as soon as i add my third and last table into play .. the query takes over 2 mins to process and uses 80+% 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;

 

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!

the user table also has an email column, thats how the user and customer tables are linked ...

 

am i doing something wrong ?  any help would be appreciated

 

Info:

Processor: 4 x Xeon 3 Ghz

OS: RedHat Linux

Memory : 2 GB

Mysql: 4.1.22-standard

[bubblegum.anarchy]

I do not see customerId in customer and no UserEmail in user (I am assuming the information below is not accurate).

 

Linking customer to user with an integer would be more appropriate.

[fenway]

Could you post the EXPLAIN output?