| Bug #94862 | MySQL optimizer scan full index for max() on indexed column. | ||
|---|---|---|---|
| Submitted: | 2 Apr 2019 9:11 | Modified: | 25 Oct 2019 12:55 |
| Reporter: | Seunguck Lee | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.15 | OS: | Any (CentOS 7) |
| Assigned to: | CPU Architecture: | Any (Linux3.10.0-229.14.1.el7.centos.plus.x86_64 #1 SMP Tue Sep 15 18:05:55 UTC 2015 x86_64 x86_64 x86_64) | |
[2 Apr 2019 14:02]
MySQL Verification Team
Hi, Thank you for your bug report. I do need you to clarify one important point. If I understand you correctly, then in all releases of 5.7, as well as in some releases of 8.0, the query: select get_timestamp(max(fd2)) from test; was as optimised as the query: select max(fd2) from test; Can you be more specific with that statement. In which releases of 8.0 (or even 5.7) have you noticed this behaviour of change ??? This is a very important question, since there were some changes in the optimisation of both MIN() and MAX() in both versions and we need to pinpoint exactly which change had this side-effect. This side-effect could be by design or by error. But, in order to determine that, we need the info that I asked you about in the above question.
[3 Apr 2019 0:23]
Seunguck Lee
Hi Sinsia. I have compared execution plan between 8.0.15 vs 5.7.21. And on MySQL 5.7.21, below two queries' execution plan show "Select tables optimized away" in Extra field. > select max(fd2) from test; > select get_timestamp(max(fd2)) from test; But on MySQL 8.0.15, below query is using "Select tables optimized away" plan. > select max(fd2) from test; But below query (using user function) use scan full index even user function is DETERMISTIC. > select get_timestamp(max(fd2)) from test;
[3 Apr 2019 13:05]
MySQL Verification Team
Hi, I am using the same macOS Mojave and 5.7.25 and this is what I get: explain select get_timestamp(max(fd2)) from test; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.01 sec) But, you did not report that there are warnings, which might be a cause why 8.0 was changed: mysql> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'COM_FIELD_LIST' is deprecated and will be removed in a future release. Please use SHOW COLUMNS FROM statement instead | +---------+------+------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) Do you get a warning too in 5.7.25 ??
[3 Apr 2019 13:20]
MySQL Verification Team
Hi, With 8.0.15, I get the following: mysql> explain select get_timestamp(max(fd2)) from test; +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | index | NULL | ix_fd2 | 83 | NULL | 9746 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) Hence, the optimiser has changed the behaviour. There is no entry in the ChangeLog that would document this change in behaviour. I will verify this as a code bug, but do notice that most likely this will finish as a documentation bug. Simply, release notes will have this change in code added. Right now, there is only an entry that is dealing with nested queries. I do not think that deterministic functions can be allowed to optimise the tables away. However, I am verifying this as a code bug, until someone comes and documents that the change was necessary. Until then ..... I am verifying this report as a code bug, just as it is reported.
[4 Apr 2019 6:03]
Seunguck Lee
Hi. I don't know why do you test with MySQL 5.7.25 on MacOS. (Have I reported this issue is happened on this environments ? why do you say "same macOS Mojave and 5.7.25".) My actual test environment is CentOS 7 + MySQL 5.7.21 (and compared with MySQL 8.0.15) And THAT warning is not displayed to me. (And I don't this "COM_FIELD_LIST" cmd is something to do with this, even my function does not use "DESC table" command). These are output of my test envrionment. (64bit CentOS 7 + MySQL 5.7.21, MySQL 8.0.15). I think you should test my test case with this env. mysql-5.7.21> explain select max(fd2) from test; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql-5.7.21> show warnings; +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select max(`TB_CS`.`test`.`fd2`) AS `max(fd2)` from `TB_CS`.`test` | +-------+------+-----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql-5.7.21> explain select get_timestamp(max(fd2)) from test; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql-5.7.21> show warnings; +-------+------+---------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `get_timestamp`(max(`TB_CS`.`test`.`fd2`)) AS `get_timestamp(max(fd2))` from `TB_CS`.`test` | +-------+------+---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql-8.0.15> explain select max(fd2) from test; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql-8.0.15> show warnings; +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select max(`TB_CS`.`test`.`fd2`) AS `max(fd2)` from `TB_CS`.`test` | +-------+------+-----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql-8.0.15> explain select get_timestamp(max(fd2)) from test; +----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | test | NULL | index | NULL | ix_fd2 | 83 | NULL | 20062 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql-8.0.15> show warnings; +-------+------+---------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `get_timestamp`(max(`TB_CS`.`test`.`fd2`)) AS `get_timestamp(max(fd2))` from `TB_CS`.`test` | +-------+------+---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
[4 Apr 2019 12:59]
MySQL Verification Team
Hi Lee, I wish I knew what is it that you want ??? This bug is already verified and transferred to the internal database for further processing. Regarding warnings and other questions, our Reference Manual can provide you with all the answers.
[5 Apr 2019 2:31]
Seunguck Lee
Hi Sinisa. I have missed your last comment. I have seen your comment you mentioned about "Warning" at the moment of writing my comment. So I wanted to say that there's no "Warnings" in my test case and my test case was not MacOS and you have tested wrong minor version.
[25 Oct 2019 12:55]
Jon Stephens
Documented fix as follows in the MySQL 8.0.19 changelog:
EXPLAIN output showed -Select tables optimized away- for a query
using MAX() on an indexed column, but if MAX() on the same
column was called in a user function, it showed -Using index-
instead.
Closed.
[25 Oct 2019 12:59]
MySQL Verification Team
Thank you, Mr. Stephens.
Description: MySQL explain result say "Select tables optimized away" on extra field for MAX() on indexed column. But if I use MAX() in another user function, MySQL optimizer scan full index not using "Select tables optimized away". But my user function(Routine) is DETERMISTIC. And MySQL 5.7 utilize "Select tables optimized away" optimization even if user function is used. explain select max(fd2) from test; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ explain select get_timestamp(max(fd2)) from test; +----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | test | NULL | index | NULL | ix_fd2 | 83 | NULL | 326928 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+ How to repeat: create table test ( id BIGINT NOT NULL AUTO_INCREMENT, fd1 BIGINT, fd2 VARCHAR(20), fd3 DATETIME, PRIMARY KEY(id), INDEX ix_fd2 (fd2) ); for(i=0; i<1000000; i++){ INSERT INTO test VALUES (NULL, i, UNIX_TIMESTAMP(), now()); } DELIMITER ;; CREATE FUNCTION get_timestamp(str VARCHAR(32)) RETURNS timestamp DETERMINISTIC SQL SECURITY INVOKER BEGIN RETURN FROM_UNIXTIME(CONVERT(str, SIGNED INTEGER)); END ;; > explain select max(fd2) from test; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ > explain select get_timestamp(max(fd2)) from test; +----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | test | NULL | index | NULL | ix_fd2 | 83 | NULL | 326928 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+ Suggested fix: Like MySQL 5.7, MySQL 8.0 also use "Select tables optimized away" optimization even if query use DETERMISTIC function(routine) with MAX() or MIN() builtin function.