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 &gt; ("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 "&lt;" 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&gt; explain select sum(diff_ms) from writetest_table where time_on &gt; '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&gt; 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)
For the specific query:

select sum(diff_ms) 
from writetest_table 
where time_on &gt; '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)