SQL. Как переписать correlation subquery в JOIN to derived table

• 04, 27, 2016
•  bullgare
•  Databases, Server Optimization
• No Comments.

Допустим есть такая таблица

CREATE TABLE `payment` (
 `payment_id` smallint(5) unsigned NOT NULL auto_increment,
 `customer_id` smallint(5) unsigned NOT NULL,
 `staff_id` tinyint(3) unsigned NOT NULL,
 `rental_id` int(11) default NULL,
 `amount` decimal(5,2) NOT NULL,
 `payment_date` datetime NOT NULL,
 `last_update` timestamp NOT NULL ... on update CURRENT_TIMESTAMP,
 PRIMARY KEY (`payment_id`),
 KEY `idx_fk_staff_id` (`staff_id`),
 KEY `idx_fk_customer_id` (`customer_id`),
 KEY `fk_payment_rental` (`rental_id`),
 CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`)
 REFERENCES `rental` (`rental_id`),
 CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`)
 REFERENCES `customer` (`customer_id`) ,
 CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`)
 REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

И нам нужно для каждого покупателя вернуть данные его последнего заказа.
Так делать очень плохо (DEPENDENT SUBQUERY, делает очень много запросов за строками):

EXPLAIN SELECT
  p.*
FROM payment p
WHERE p.payment_date = (
  SELECT MAX(payment_date)
  FROM payment
  WHERE customer_id=p.customer_id
)

Делать надо так:

EXPLAIN SELECT
  p.*
FROM (
  SELECT customer_id, MAX(payment_date) as last_order
  FROM payment
  GROUP BY customer_id
) AS last_orders
INNER JOIN payment p
  ON p.customer_id = last_orders.customer_id
  AND p.payment_date = last_orders.last_order

Тогда запросов будет всего два.

Jay Pipes, «SQL Query Tuning: The Legend of Drunken Query Master», слайды 56-59.(http://www.slideshare.net/ZendCon/sql-query-tuning-the-legend-of-drunken-query-master-presentation)