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