Query Slower after Rows Deleted I have a Table that stored GPS data from vehicle trackers.

There are 8 vehicles tracked via an API. Each time the information is returned from the web server each of the vehicles has its information stored in the table. The table is pinged every 10 seconds. So 8 new rows are added every 10 seconds.

I was doing a backup and noticed that the DB had grown from 100mb to approx ~3Gb.

I deleted all rows from the GPS table where the TimeStamp was older than 30 days which took the row count from 4 million down to 2.5 million. 

The query to pull historical vehicle data was typically taking about 5 seconds before the purge; after it, it times out.

This is the query that displays dates for which vehicle data is available:

SELECT CAST(TIMESTAMP AS DATE) AS `Vehicle_Date` 
FROM `GPSLog` 
GROUP BY `Vehicle_Date` 
ORDER BY TIMESTAMP DESC


This is the query that is now timing out:

SELECT JSONString, TimeStamp, DATE(TimeStamp) as JDate, ID
FROM BusinessManager.GPSLog
Where IMEI = @ID
  AND DATE(TimeStamp) = @D1


I have tried OPTIMIZE TABLE GPSLog and increasing the Buffer_Pool_Size but neither of these things have resolved the issue.

Any anybody suggests any alternative actions I can take?

The table structure is:

ID (INT 10) PK/AI 
IMEI (Big INT 20) 0
JSONString (Json) NULL
TImeStamp (TimeStamp) CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP


Indexes:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
GPSLog  0   PRIMARY 1   ID  A   2055001             BTREE       
GPSLog  1   Vehicle 1   TimeStamp   A   367903          YES BTREE       
GPSLog  1   Vehicle 2   IMEI    A   2055252         YES BTREE   


Table, Create Table

GPSLog 

CREATE TABLE `GPSLog` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `IMEI` bigint(20) DEFAULT '0',
  `JSONString` json DEFAULT NULL,
  `TimeStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Vehicle` (`TimeStamp`,`IMEI`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5533794 DEFAULT CHARSET=utf8


EXPLAIN

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'GPSLog', NULL, 'ALL', NULL, NULL, NULL, NULL, '2055356', '100.00', NULL
ANALYZE TABLE .. may refresh the statistics such that queries run faster.  (Defragging (OPTIMIZE TABLE) takes a long time and is probably overkill.)
For future purging, implement PARTITION BY RANGE; see this for discussion.
The first query would be faster if the ORDER BY said the same thing as the GROUP BY.
Instead of AND DATE(TimeStamp) = @D1, say AND Timestamp &gt;= @D1 AND Timestamp &lt; @D1 + INTERVAL 1 DAY, plus have composite INDEX(IMEI, Timestamp).  (The index you have is not as efficient for this query.)
innodb_buffer_pool_size should be somewhere around 70% of RAM.  Increasing it too much will lead to swapping, which is terrible for performance.