Optimizing WHERE condition for TIMESTAMP field in MySQL SELECT statement

Question

I am working on a schema for an analytics system which tracks usage times, and there is a need to see total usage time in a certain date range.

To give a simple example, this type of query would be run often:

select sum(diff_ms) from writetest_table where time_on > ("2015-07-13 15:11:56");

This query typically takes around 7 seconds on a table that is heavily populated. It has ~35 million rows, MyISAM on MySQL running on Amazon RDS (db.m3.xlarge).

Getting rid of the WHERE clause makes the query take only 4 seconds, and adding a second clause (time_off > XXX) adds an additional 1.5 seconds, bringing the query time to 8.5 seconds.

Since I know these types of queries will be commonly done I would like to optimize things so they are faster, ideally below 5 seconds.

I started by adding an index on time_on, and though that drastically sped up a WHERE "=" query, it had no effect on the ">" query. Is there a way to create an index that would speed up the WHERE ">" or "<" queries?

Or if there are any other suggestions the performance of this type of query, please let me know.

Note: I am using the "diff_ms" field as a denormalization step (it equals time_off - time_on) which improves performance of the aggregation by around 30%-40%.

I am creating the index with this command:

ALTER TABLE writetest_table ADD INDEX time_on (time_on) USING BTREE;

Running "explain" on the original query (with "time_on >") says time_on is a "possible_key" and the select_type is "SIMPLE". The "extra" column says "Using where", and "type" is "ALL". After the index was added, the table says "time_on" is "MUL" key type, which seems correct since the same time can be present twice.

Here is the table schema:

CREATE TABLE `writetest_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sessionID` int(11) DEFAULT NULL,
  `time_on` timestamp NULL DEFAULT NULL,
  `time_off` timestamp NULL DEFAULT NULL,
  `diff_ms` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time_on` (`time_on`)
) ENGINE=MyISAM AUTO_INCREMENT=50410902 DEFAULT CHARSET=latin1;

UPDATE: I created the following index based on ypercube's response, but this increases the query time for the first query to around 17 seconds!

ALTER TABLE writetest_table  ADD INDEX time_on__diff_ms__ix (time_on, diff_ms) ;

UPDATE 2: EXPLAIN output

mysql> explain select sum(diff_ms) from writetest_table where time_on > '2015-07-13 15:11:56';
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
| id | select_type | table               | type  | possible_keys        | key                  | key_len | ref  | rows     | Extra                    |
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | writetest_table_old | index | time_on__diff_ms__ix | time_on__diff_ms__ix | 10      | NULL | 35831102 | Using where; Using index |
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

Update 3: result of requested query

mysql> SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1;
+---------------------+
| time_on             |
+---------------------+
| 2015-07-13 15:11:56 |
+---------------------+
1 row in set (0.01 sec)

Answer 1

I think I am starting to understand.

When I asked you to run

SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1;

You said it was 2015-07-13 15:11:56 which you have in your WHERE clause

When you did the query

select sum(diff_ms) from writetest_table;

It performed a full table scan of 35.8 million rows.

When you did the query

select sum(diff_ms) from writetest_table where time_on > ("2015-07-13 15:11:56");

It performed a full index scan of 35.8 million rows.

It totally makes sense that the query without the WHERE clause is faster. Why ?

The table scan would read 35.8 million rows in one linear pass.

The EXPLAIN on the query with the WHERE also turned up 35.8 million rows. An index scan would behave a little different. While the BTREE keeps the order of the keys, it is horrible for doing range scans. In your particular case, you are performing the worst possible range scan, which would have the same number of BTREE entries as there are rows in the table. MySQL has to traverse the BTREE pages (at least across the leaf nodes) to read the values. In addition, the time_on column has to be compared along the way in the order dictated by the index. Therefore, non-leaf BTREE nodes must be traversed as well.

Please see my posts on BTREEs

• Aug 06, 2013 : In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index
• Jun 28, 2012 : Benefits of BTREE in MySQL

If the query was as of midnight today

select sum(diff_ms) from writetest_table where time_on >= ("2015-07-14 00:00:00");

or even noon today

select sum(diff_ms) from writetest_table where time_on >= ("2015-07-14 12:00:00");

it should take less time.

MORAL OF THE STORY : Do not use a WHERE clause that does an ordered range scan equal to the number of rows in the target table.

Answer 2

For the specific query:

select sum(diff_ms) 
from writetest_table 
where time_on > '2015-07-13 15:11:56' ;     -- use single quotes, not double

an index on (time_on, diff_ms) would be the best option. So, if the query runs often enough or its efficiency is crucial to your application, add this index:

ALTER TABLE writetest_table 
  ADD INDEX time_on__diff_ms__ix      -- pick a name for the index
    (time_on, diff_ms) ;

---

_{(Not related to the question)
And really, change the table's engine to InnoDB. It's 2015 and MyISAM's funeral was some years ago.
(/rant)}