0

I wanted to check if my server's queries are properly using index. So I enabled log_queries_not_using_indexes, and ran test-cases.

I found out that the following query is written on mysql.slow_log table.

SELECT * FROM user_rel 
WHERE (f = '5837be9dc34f747dbd7ba6c7' AND t = '5837be9dc34f747dbd7ba6ca') 
OR (f = '5837be9dc34f747dbd7ba6ca' AND t = '5837be9dc34f747dbd7ba6c7') 
LIMIT 2;

But my EXPLAIN EXTENDED result seems ok.

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'user_rel', 'range', 'f-t-UNIQUE,t_uid_idx', 'f-t-UNIQUE', '208', NULL, '2', '100.00', 'Using index condition'

Please someone can explain me why this query is written as slow_log?

Edit 1

Here are mysql options regarding slow query logging.

log_output  TABLE
log_queries_not_using_indexes   ON
long_query_time 2.000000
min_examined_row_limit  0
slow_query_log  ON
sql_log_off OFF

Edit 2

After I updated min_examined_row_limit to 10, the query doesn't show up anymore. But why is this affect the log_queries_not_using_indexes result?

redism
  • 500
  • 7
  • 18

1 Answers1

0

Hmmm... smells like another reason to avoid log_queries_not_using_indexes.

If f and t are symmetric:

The table would be more efficiently queried if you 'sorted' the two columns before storing them. You would not need to do the OR and it could more efficiently use the obvious index.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • So you're suggesting keeping only one row for unique `f`, `t` pair. I'll try to refactor as you suggest. Thank you. – redism Nov 29 '16 at 05:06