Bug #29995 SET GLOBAL log_queries_not_using_indexes = 1 does nothing.
Submitted: 24 Jul 2007 1:29 Modified: 24 Jul 2007 6:08
Reporter: Tobias Asplund Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Logging Severity:S3 (Non-critical)
Version:5.1.20 OS:Linux
Assigned to: CPU Architecture:Any

[24 Jul 2007 1:29] Tobias Asplund 
Description:
Modifying the setting log-queries-not-using-indexes does nothing if you change it run-time.

[mysql@hitomi logs]$ cat slow_query.log 

[mysql@hitomi logs]$ mysql -e "SELECT COUNT(*) FROM tobias.City2 WHERE Id = 200"
+----------+
| COUNT(*) |
+----------+
|      512 | 
+----------+

[mysql@hitomi logs]$ mysql -e "EXPLAIN SELECT COUNT(*) FROM tobias.City2 WHERE Id = 200"
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | City2 | ALL  | NULL          | NULL | NULL    | NULL | 2074112 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

[mysql@hitomi logs]$ mysql -e "SET GLOBAL log_queries_not_using_indexes=1"

[mysql@hitomi logs]$ mysql -e "EXPLAIN SELECT COUNT(*) FROM tobias.City2 WHERE Id = 200"

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | City2 | ALL  | NULL          | NULL | NULL    | NULL | 2074112 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

[mysql@hitomi logs]$ mysql -e "SELECT COUNT(*) FROM tobias.City2 WHERE Id = 200"
+----------+
| COUNT(*) |
+----------+
|      512 | 
+----------+

[mysql@hitomi logs]$ cat slow_query.log 

[mysql@hitomi logs]$ 

How to repeat:
enable the slow query log, make sure log-queries-not-using-indexes is not enabled.

start the server with those options, run a table scan query.
run SET GLOBAL log_queries_not_using_indexes=1;
run table scan query again, verify it's not being logged.
[24 Jul 2007 1:38] Marc ALFF 
Was the query cache used ?

If so, the second query is not executed, so this would be the expected result.
[24 Jul 2007 2:27] Tobias Asplund 
mysql> show variables like 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     | 
+------------------+-------+
[24 Jul 2007 6:08] Sveta Smirnova 
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of Bug #28808