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

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

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

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

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)

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

Similar Posts

LEAVE A COMMENT Отменить ответ