MYSQL 5.1.34, looked through the release notes for more recent versions and there wasn't anything that stood out. Is the innodb plug in worth using?
When the query runs thats causing disk I/O goes to around 20%wa. Far higher than the 2-5% it usually sits around. Load average on the server is also between 1 and 1.5 except for this query. CPU usage does rise although its mostly I/O.
I'll try use index on it as it chooses vehicle_id,date_time.
Is it normal for the indexes to take up more disk space than table space?
SELECT s.schema_name,t.table_name, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") data_size,CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb") index_size, t.ENGINE ENGINE, t.table_rows TABLE_ROWS,t.row_format TABLE_ROW_FORMAT,date(t.update_time) FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in ("mysql","information_schema") GROUP BY s.schema_name,t.table_name,TABLE_ROW_FORMAT,ENGINE ORDER BY TABLE_ROWS DESC,data_size DESC,index_size DESC;
seems to show huge index sizes.