| Bug #16016 | Problem with optimization of SELECT MAX() FROM view | ||
|---|---|---|---|
| Submitted: | 27 Dec 2005 12:34 | Modified: | 13 Jan 2006 0:14 |
| Reporter: | Victoria Reznichenko | Email Updates: | |
| Status: | Closed | ||
| Category: | Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 5.0.18 | OS: | Linux (linux) |
| Assigned to: | Igor Babaev | Target Version: | |
[27 Dec 2005 12:35]
Victoria Reznichenko
table t1
Attachment: t1.zip (application/zip, text), 190.92 KiB.
[7 Jan 2006 6:28]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/733
[12 Jan 2006 6:47]
Igor Babaev
ChangeSet 1.2021 06/01/06 22:28:26 igor@rurik.mysql.com +3 -0 Fixed bug #16016: MIN/MAX optimization was not applied to views. sql/opt_sum.cc 1.49 06/01/06 22:28:02 igor@rurik.mysql.com +5 -5 Fixed bug #16016: MIN/MAX optimization was not applied to views. The fix employs the standard way of handling direct references to view fields. mysql-test/t/view.test 1.132 06/01/06 22:28:02 igor@rurik.mysql.com +24 -0 Added a test case for bug #16016. mysql-test/r/view.result 1.143 06/01/06 22:28:02 igor@rurik.mysql.com +29 -0 Added a test case for bug #16016. The fix will appear in 5.0.20
[13 Jan 2006 0:14]
Mike Hillyer
Added entry to 5.0.20 changelog:
<listitem>
<para>
<literal>MIN()</literal> and <literal>MAX()</literal>
operations were not optimized for views. (Bug #16016)
</para>
</listitem>
[13 Jan 2006 17:52]
Paul DuBois
The changelog version is actually 5.0.19.
Description: SELECT MAX() from view is executed slower than SELECT MAX() from base_table: mysql> select max(id) from v1; +---------+ | max(id) | +---------+ | 100000 | +---------+ 1 row in set (0.32 sec) mysql> select max(id) from t1; +---------+ | max(id) | +---------+ | 100000 | +---------+ 1 row in set (0.00 sec) Explain output for those queries are also different: mysql> explain select max(id) from v1; +----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | PRIMARY | t1 | index | NULL | PRIMARY | 4 | NULL | 100000 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+ 1 row in set (0.00 sec) mysql> explain select max(id) from t1; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ 1 row in set (0.00 sec) How to repeat: 1. load table t1 2. create view: mysql> show create view v1; +------+----------------------------------------------------------------------------------------------------------------------------+ | View | Create View | +------+----------------------------------------------------------------------------------------------------------------------------+ | v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id` from `t1` | +------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 3. run queries: select max(id) from t1; select max(id) from v1; explain select max(id) from t1; explain select max(id) from v1;