| Bug #29244 | log-queries-not-using-indexes logs all UNION SELECTs to slow query log | ||
|---|---|---|---|
| Submitted: | 20 Jun 2007 16:12 | ||
| Reporter: | Axel Schwenke | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Logging | Severity: | S4 (Feature request) |
| Version: | 4.1.22, 5.0.44, 5.1.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Nov 2010 6:35]
Michael Furdyk
This is severely polluting and growing our slow query log, so just wanted to add a +1 to the usefulness of this!
[21 Jan 2011 7:53]
Michal Kovacik
I have to agree with you. Logging this type of UNION queries is completely useless.
[30 Jan 2012 0:09]
Gavin Stark
+1 the individual queries comprising the UNIONs should be considered for index usage
[19 Apr 2012 4:03]
jacky leung
+1 For this issue, this have cause a lot of false positive alert if using monitoring base on slow queries status
Description: When using the log-queries-not-using-indexes option, all UNION SELECTs will be logged to the slow query log, even when they were fast and using indexes. How to repeat: my.cnf: [mysqld] log-slow-queries log-queries-not-using-indexes mysql> create table t1 (c1 int, index (c1)); Query OK, 0 rows affected (0,04 sec) mysql> insert into t1 values (1), (2), (3), (4), (5); Query OK, 5 rows affected (0,01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 where c1=2 union select * from t1 where c1=3\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: ref possible_keys: c1 key: c1 key_len: 5 ref: const rows: 1 Extra: Using where; Using index *************************** 2. row *************************** id: 2 select_type: UNION table: t1 type: ref possible_keys: c1 key: c1 key_len: 5 ref: const rows: 1 Extra: Using where; Using index *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: <union1,2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 3 rows in set (0,00 sec) mysql> select * from t1 where c1=2 union select * from t1 where c1=3; +------+ | c1 | +------+ | 2 | | 3 | +------+ 2 rows in set (0,00 sec) in slow-query.log: # Time: 070620 17:53:17 # User@Host: root[root] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 2 Rows_examined: 7 use test; select * from t1 where c1=2 union select * from t1 where c1=3; Suggested fix: SELECT ... UNION statements should only be logged to the slow query log if any of the single SELECT statements would be logged.