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

I have one table – users

mysql> select count(1) from users;
+----------+
| count(1) |
+----------+
|   389900 |
+----------+
1 row in set (0.08 sec)

and doing one query:

mysql> SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----------+
| COUNT(*) |
+----------+
|      913 |
+----------+
1 row in set (0.23 sec)

and slow-log complains

# Schema: test  Last_errno: 0  Killed: 0
# Query_time: 0.365658  Lock_time: 0.000060  Rows_sent: 1  Rows_examined: 389894  Rows_affected: 0
# Bytes_sent: 65
SET timestamp=1389055417;
SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:23:36') AND (last_logoff <> last_active_at or last_logoff is null);

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

so, let’s explain

mysql> explain SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 385645 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

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

mysql> alter table users add index ix_last_logoff (last_logoff);
Query OK, 0 rows affected (2.16 sec)

mysql> explain SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----+-------------+-------+------+----------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys  | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+----------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | ix_last_logoff | NULL | NULL    | NULL | 385646 | Using where |
+----+-------------+-------+------+----------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

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

mysql> alter table users drop index ix_last_logoff;
Query OK, 0 rows affected (1.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table users add index ix_last_logoff (last_logoff, last_active_at);
Query OK, 0 rows affected (2.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

and what our explain friend shows now?

mysql> explain SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----+-------------+-------+-------+----------------+----------------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | users | index | ix_last_logoff | ix_last_logoff | 12      | NULL | 385646 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----------+
| COUNT(*) |
+----------+
|      913 |
+----------+
1 row in set (0.23 sec)

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

mysql> alter table users drop index ix_last_logoff;
Query OK, 0 rows affected (3.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table users add index ix_last_logoff (last_active_at, last_logoff);                                                         
Query OK, 0 rows affected (3.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

What explain says now?

mysql> explain SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | users | range | ix_last_logoff | ix_last_logoff | 6       | NULL |  963 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Great! and select?

mysql> SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----------+
| COUNT(*) |
+----------+
|      924 |
+----------+
1 row in set (0.00 sec)

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.