MySQL, query being logged and I don't know why

Forum Moderators: open

Message Too Old, No Replies

MySQL, query being logged and I don't know why

csdude55

3:43 am on Jan 17, 2016 (gmt 0)

In my.cnf, I have the following settings:


log-queries-not-using-indexes
slow_query_log_file=/tmp/mysqld.slow.log
slow_query_log=1
long_query_time=1
log-error=/tmp/mysqld.error.log

Immediately I had several of these queries show up in mysqld.slow.log:


# Query_time: 0.000283 Lock_time: 0.000063 Rows_sent: 0 Rows_examined: 0
SET timestamp=1453001358;
SELECT options, 'var1' FROM tableA WHERE id=2049020229
UNION
SELECT options, 'var2' FROM tableB WHERE id=2049020229;

(breaks added for legibility)

EXPLAIN gives me 3 rows of data. The first two show that the PRIMARY key is used, and the third (UNION RESULT) shows NULL under both "possible keys" and keys".

So I'm confused. Am I correct that the query is correctly using the indexes? And that the query time was less than 5 seconds (0.000283 seconds)? If so, why is it showing up in the slow log?

jadebox

7:12 pm on Feb 8, 2016 (gmt 0)

When you used "log-queries-not-using-indexes" queries done without indexes are logged as well as slow ones. Your query is logged because it uses a UNION and there's no index used when doing the query. MySQL isn't able to use indexes because of the UNION.

In your example there cannot be duplicate records (because of the literal values being returned), so I think you can use UNION ALL to speed up the query and avoid it being logged.