MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query
Posted by: Jay Pipes
Date: March 15, 2006 11:37AM

Hi Jorge!

You may find that the following performs much quicker than the correlated subquery you use above. It uses a single derived table (one select) instead of correlated subqueries (one select *per* record in the outer table), and so should use much fewer resources:

SELECT registo.id_reg, dp.data_max as data_pagamento
FROM registo
INNER JOIN (
SELECT id_reg, MAX(data_pagamento) as data_max
FROM pagamento
GROUP BY id_reg
) as dp
ON dp.id_reg = registo.id_reg;

You'll notice I removed the DISTINCT, since you now do not need any grouping on the outer query, and I removed the LEFT JOIN to pagamento since it was not used anywhere in the query.

Cheers,

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Views
Written By
Posted
2672
March 15, 2006 11:08AM
1939
March 15, 2006 11:17AM
Re: Query
1910
March 15, 2006 11:37AM
1812
March 17, 2006 08:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.