Home » Performance » MySQL » Bad Performance or Bad Query ?
icon5.gif  Bad Performance or Bad Query ? [message #1056] Mon, 09 April 2007 13:15 Go to next message
zeep25  is currently offline zeep25
Messages: 2
Registered: April 2007
Junior Member
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.FirstName
FROM 
	`receipts`, 
	`customer`
WHERE 
	receipts.customerId = customer.CustomerId
GROUP BY 
	receipts.customerId
ORDER BY 
	receipts.recID DESC
LIMIT 
	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 ...

[Updated on: Mon, 09 April 2007 16:07]

Re: Bad Performance or Bad Query ? [message #1057 is a reply to message #1056 ] Mon, 09 April 2007 13:36 Go to previous messageGo to next message
zeep25  is currently offline zeep25
Messages: 2
Registered: April 2007
Junior Member
oh and i forgot ... system info:

Processor: 4 x Xeon 3 Ghz
OS: RedHat Linux
Memory : 2 GB
Mysql: 4.1.22-standard
Re: Bad Performance or Bad Query ? [message #1258 is a reply to message #1056 ] Tue, 15 May 2007 07:39 Go to previous message
EveryonE  is currently offline EveryonE
Messages: 1
Registered: May 2007
Location: Moscow
Junior Member
Show me result for EXLAIN SELECT for both queries.

Example

EXPLAIN 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;
Previous Topic:Server statistics - COM_SET_OPTION
Next Topic:Mysql not using index for ORDER BY ?
Goto Forum:
  



Current Time: Tue Jan 6 06:51:41 EST 2009

Total time taken to generate the page: 0.01209 seconds
.:: Contact :: Home :: MySQL Support by Percona.com ::.

Powered by: FUDforum 2.7.5.
Copyright ©2001-2006 FUD Forum Bulletin Board Software

MySQL Performance | Forum authority Badge