SET STATEMENT min_examined_row_limit has no effect

[Elena Stepanova]

MySQL [test]> set global log_output = 'TABLE', global slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set min_examined_row_limit = 0;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> truncate table mysql.slow_log;
Query OK, 0 rows affected (0.17 sec)

MySQL [test]> set long_query_time = 0.000001;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1);
+------------+
| sleep(0.1) |
+------------+
| 0 |
+------------+
1 row in set (0.37 sec)

MySQL [test]> select * from mysql.slow_log;
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| 2014-11-02 13:45:33 | root[root] @ localhost [::1] | 00:00:00 | 00:00:00 | 0 | 0 | test | 0 | 0 | 20 | set long_query_time = 0.000001 | 5 |
| 2014-11-02 13:45:33 | root[root] @ localhost [::1] | 00:00:00 | 00:00:00 | 1 | 1 | test | 0 | 0 | 20 | set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1) | 5 |
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
2 rows in set (0.00 sec)

MySQL [test]> set min_examined_row_limit = 50;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select sleep(0.1) union select sleep(0.1);
+------------+
| sleep(0.1) |
+------------+
| 0 |
+------------+
1 row in set (0.20 sec)

MySQL [test]> select * from mysql.slow_log;
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| 2014-11-02 13:45:33 | root[root] @ localhost [::1] | 00:00:00 | 00:00:00 | 0 | 0 | test | 0 | 0 | 20 | set long_query_time = 0.000001 | 5 |
| 2014-11-02 13:45:33 | root[root] @ localhost [::1] | 00:00:00 | 00:00:00 | 1 | 1 | test | 0 | 0 | 20 | set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1) | 5 |
| 2014-11-02 13:45:33 | root[root] @ localhost [::1] | 00:00:00 | 00:00:00 | 2 | 2 | test | 0 | 0 | 20 | select * from mysql.slow_log | 5 |
+---------------------+------------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

[Valerii Kravchuk (valerii-kravchuk)]

Reproduced on 5.6.21-70:

[openxs@centos ~]$ mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21-70.0 Percona Server (GPL), Release 70.0, Revision 688

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global log_output = 'TABLE', global slow_query_log = 1;
Query OK, 0 rows affected (0.08 sec)

mysql> set min_examined_row_limit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table mysql.slow_log;
Query OK, 0 rows affected (0.04 sec)

mysql> set long_query_time = 0.000001;
Query OK, 0 rows affected (0.01 sec)

mysql> set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1);
+------------+
| sleep(0.1) |
+------------+
| 0 |
+------------+
1 row in set (0.23 sec)

mysql> select * from mysql.slow_log;
+---------------------+---------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+---------------------+---------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
| 2014-11-02 15:30:22 | root[root] @ localhost [] | 00:00:00 | 00:00:00 | 0 | 0 | test | 0 | 0 | 0 | set long_query_time = 0.000001 | 2 |
| 2014-11-02 15:30:34 | root[root] @ localhost [] | 00:00:00 | 00:00:00 | 1 | 1 | test | 0 | 0 | 0 | set statement min_examined_row_limit = 50 for select sleep(0.1) union select sleep(0.1) | 2 |
+---------------------+---------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------+-----------+
2 rows in set (0.00 sec)

mysql> set min_examined_row_limit = 50;
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep(0.1) union select sleep(0.1);
+------------+
| sleep(0.1) |
+------------+
| 0 |
+------------+
1 row in set (0.20 sec)

mysql> select * from mysql.slow_log;
+---------------------+---------------------------+------------+-----------+-----------+---------...

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1576