Welcome to WebmasterWorld Guest from 18.204.48.199

Forum Moderators: open

Message Too Old, No Replies

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

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

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1205
votes: 120


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?
7:12 pm on Feb 8, 2016 (gmt 0)

Junior Member

10+ Year Member

joined:June 24, 2004
posts:147
votes: 1


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.