Optimizing WHERE condition for TIMESTAMP field in MySQL SELECT statement

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 "") 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';
+--

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)

StackExchange Database Administrators Q#106880, answer score: 4