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:
None 
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
Triage: Triaged: D5 (Feature request)

[20 Jun 2007 16:12] Axel Schwenke
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.
[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