As long as you are rebuilding the table, you should improve the datatypes...
INT is always 4 bytes;
MEDIUMINT is 3 bytes;
SMALLINT is 2 bytes;
TINYINT is 1 byte;
FLOAT is 4 bytes;
time` int(11) DEFAULT NULL, --> see the TIME datatype (assuming it really is a time not a datetime); 3 bytes.
`rush_hour_am` int(11) DEFAULT '0',
`status` int(11) DEFAULT '0', --> TINYINT UNSIGNED?
In general, make int-like fields UNSIGNED.
`deceleration_1/2/3` int(11) DEFAULT '0', -- perhaps it should be in another table? ("arrays" are not implemented.)
etc.
`latitude` decimal(15,10) DEFAULT NULL, -- gross overkill; takes about 8 bytes; more discussion here:
http://mysql.rjweb.org/doc.php/latlng#representation_choices
> I agree >> Joining FROM t1 > t2 WHERE t1.id = t2.trip_id is going to be the killer just to get a date <<
It would be much less of a killer if you used a compound index something like
(trip_id, date)
`created_at` datetime NOT NULL, -- DATETIME is 8 bytes; TIMESTAMP (same resolution) is 4 bytes.
`highest_speed` float DEFAULT '0', --> SMALLINT UNSIGNED?
Please show us the query with the real table/column names; then we can reassess the situation.