Index? Yes, please

When altering some tables, I got into optimization adding indexes.

I have one table – users

and doing one query:

and slow-log complains

from his you can see it did full table scan (Rows_examined: 389894) which is kinda wrong.

so, let’s explain

Hmm, “type” shows no index has been used and ALL table scanned. WRONG

Let’s add an index – we have two fields, last_active_at and last_logoff, let’s try with last_logoff only

hmm, still using full table scan. Let’s drop and create combined index

and what our explain friend shows now?

Oops. Explain shows it’s using index (bit better than ALL), but still fetches 385646 rows. Still wrong. Let’s switch columns in index.

What explain says now?

Great! and select?

0.20secs improvement.

Explanation? It’s simple. last_active_at is doing range scan against a value and against second column, thus first referenced. If last_logoff is first in combined index, it can’t be used for last_active_at > part of the query.