Slow query log of wp_options WHERE autoload = 'yes' Slow query log:

# User@Host: 
# Query_time: 181.648376 Lock_time: 2.114138 Rows_sent: 1425838 Rows_examined: 1663532
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';


Table has only 650 rows.
Any ideas how can this happen?
It is impossible that a query like that SELECT ... FROM table WHERE column = value
examines 1663532 rows and sends to the SQL layer 1425838 when the table has only 650 rows. Worst case scenario, it would do a full table scan, examining 650 rows and sending all of them if all of them have autoload = yes.

So:


The query log is incorrect (seems to be truncated, maybe it corresponds to another query?)
The table had that number of rows at some time in the past, on a different session, or as a temporary table over the real query (it was locked for 2 seconds, so probably there is some write-like activity there).


Please check the result of that select with EXPLAIN and with its handler operations.

Also you can check on the slow log or binary log to detect some ongoing write activity at that time.