Bug #30659 | 300% Performance Regression 5.0.45 -> 5.2.6 on Correlated Subquery | ||
---|---|---|---|
Submitted: | 28 Aug 2007 1:03 | Modified: | 31 Aug 2007 9:59 |
Reporter: | Jay Pipes | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.2.6 | OS: | Linux (2.6.20) |
Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
Tags: | Optimizer, performance, subquery |
[28 Aug 2007 1:03]
Jay Pipes
[28 Aug 2007 19:04]
Sveta Smirnova
Thank you for the report. Verified as described on Linux using last BK sources. Mac version is not affected (both 5.0 and 5.2 shows same results like 5.2 on Linux)
[30 Aug 2007 17:30]
Gleb Shchepa
Tested on 32bit Linux with BUILD/compile-pentium-max (no debugging code). Results are same on 5.0 and 5.2: --------------------------------------------------------------------------- mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.50 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT p.payment_id, p.amount, p.payment_date, c.first_name, c.last_name FROM payment p INNER JOIN customer c ON p.customer_id = c.customer_id WHERE p.payment_date = (SELECT MAX(payment_date) FROM payment WHERE payment.customer_id = p.customer_id )\G ... 623 rows in set (0.88 sec) --------------------------------------------------------------------------- mysql> select version(); +-------------+ | version() | +-------------+ | 5.2.6-alpha | +-------------+ 1 row in set (0.00 sec) mysql> SELECT p.payment_id, p.amount, p.payment_date, c.first_name, c.last_name FROM payment p INNER JOIN customer c ON p.customer_id = c.customer_id WHERE p.payment_date = (SELECT MAX(payment_date) FROM payment WHERE payment.customer_id = p.customer_id )\G ... 623 rows in set (0.90 sec) =========================================================================== mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.50 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT p.payment_id, p.amount, p.payment_date, c.first_name, c.last_name FROM payment p INNER JOIN ( SELECT customer_id, MAX(payment_date) AS payment_date FROM payment GROUP BY customer_id ) AS last_orders ON p.customer_id = last_orders.customer_id AND p.payment_date = last_orders.payment_date INNER JOIN customer c ON p.customer_id = c.customer_id\G ... 623 rows in set (0.06 sec) --------------------------------------------------------------------------- mysql> select version(); +-------------+ | version() | +-------------+ | 5.2.6-alpha | +-------------+ 1 row in set (0.00 sec) mysql> SELECT p.payment_id, p.amount, p.payment_date, c.first_name, c.last_name FROM payment p INNER JOIN ( SELECT customer_id, MAX(payment_date) AS payment_date FROM payment GROUP BY customer_id ) AS last_orders ON p.customer_id = last_orders.customer_id AND p.payment_date = last_orders.payment_date INNER JOIN customer c ON p.customer_id = c.customer_id\G ... 623 rows in set (0.06 sec)