Query not using indexes I have a query which is not using the timestamp  index and scanning 200 million records. I know that the query will not use the index if there is a function applied on top of it. However is there any other way to re-write the query and it can use the index.

    SELECT 
*
FROM `collect_location` 
where DATE_ADD(timestamp,INTERVAL -1*-6 HOUR) &gt;= '2016-02-24 15:55:01' 
and DATE_ADD(timestamp,INTERVAL  -1*-6 HOUR) &lt;= '2016-02-24 16:15:01'\G

 explain SELECT *
    -&gt; FROM `collect_location` where DATE_ADD(timestamp,INTERVAL -1*-6 HOUR) &gt;= '2016-02-24 15:55:01' and DATE_ADD(timestamp,INTERVAL  -1*-6 HOUR) &lt;= '2016-02-24 16:15:01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: collect_location
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 24381470
        Extra: Using where
1 row in set (0.00 sec)

show create table collect_location\G
*************************** 1. row ***************************
       Table: collect_location
Create Table: CREATE TABLE `collect_location` (
  `device_id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `timestamp` datetime(3) NOT NULL,
  `location_provider` varchar(128) DEFAULT NULL,
  `collect_altitude` float DEFAULT '0',
  `session_id` varchar(128) DEFAULT NULL,
  `x_param` float(16,8) DEFAULT '0.00000000',
  `y_param` float(16,8) DEFAULT '0.00000000',
  `testcase_id` varchar(128) DEFAULT '',
  `last_location_time` datetime DEFAULT NULL,
  `location_tags` varchar(50) DEFAULT NULL COMMENT 'tag field',
  PRIMARY KEY (`device_id`,`job_id`,`timestamp`),
  KEY `fk_collect_location_job1` (`job_id`),
  KEY `collection_location.timestamp` (`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
DATE_ADD(timestamp,INTERVAL -1*-6 HOUR) &gt;= '2016-02-24 15:55:01' 


is the same as

timestamp &gt;= '2016-02-24 15:55:01' - INTERVAL -1*-6 HOUR


(You might consider changing that triple-negative into a single-negative.)

A side note about float(16,8) -- Don't use (m,n) notation for FLOAT or DOUBLE; it leads to an extra rounding error and limits the range.  Alternatively you could use DECIMAL(16,8).