MySQL log-queries-not-using-indexes and UNION false positive or error?

Question

Am activated slow queries log with this parameters:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 10
log-queries-not-using-indexes

Now it seems that MySQL is logging only query without index

this is a sample of log

# Time: 130709 15:33:54
# User@Host: xxxxxx[yyyyyy] @ localhost []
# Query_time: 0.000467  Lock_time: 0.000105 Rows_sent: 1  Rows_examined: 0
SET timestamp=1373376834;
SELECT count(`id`) as `totale` FROM ( SELECT `id` FROM `gg_evento` WHERE `team_home` = 51620 AND `status_home` = 3 AND `status_guest` = 3 UNION ALL SELECT `id` FROM `gg_evento` WHERE `team_guest` = 51620 AND `status_home` = 3 AND `status_guest` = 3) AS sq2 WHERE 1 LIMIT 1;

However, this query was optimized.

mysql> explain SELECT count(`id`) as `totale` FROM ( SELECT `id` FROM `gg_evento` WHERE `team_home` = 51620 AND `status_home` = 3 AND `status_guest` = 3 UNION ALL SELECT `id` FROM `gg_evento` WHERE `team_guest` = 51620 AND `status_home` = 3 AND `status_guest` = 3) AS sq2 WHERE 1 LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: gg_evento
         type: ref
possible_keys: fk_evento_team_home,IDX_PARTITE_NON_TERMINATE,IDX_CARICA_SFIDANTI
          key: fk_evento_team_home
      key_len: 4
          ref: 
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: gg_evento
         type: ref
possible_keys: IDX_TOTALE_GIOCATE_GUEST
          key: IDX_TOTALE_GIOCATE_GUEST
      key_len: 13
          ref: 
         rows: 1
        Extra: Using where; Using index
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: 
4 rows in set (0.00 sec)

I see into MySQL documentation this warning:

This option does not necessarily mean that no index is used

But...

1. i don't understand if the logged query is right or i have optimization problem that could be fixed
2. in case this query is a false positive, there is a way to avoid that mysql log it?

it seems that MySQL log as a no index query all queries that contain an UNION, i searched about it but i did't find nothing.

Any ideas about it? Thanks advance Michele

Answer 1

You could try min_examined_row_limit (https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_min_examined_row_limit)

As your example shows that "Rows_examined: 0", setting the a/m variable can suppress logging.

set global  min_examined_row_limit = 2;