Re: optimising a create table as select

[Peter Brawley]

I would first optimise the select without the Create section ...

- change the date_sub(sysdate...) call to a literal value
- index t1 on (id,created_at), t2 on trip_id

Consider doing this test with a subset copied to another table, to see how optimising changes performance. Remember to turn caching off ...

SET LOCAL query_cache_type = 0;
SET GLOBAL query_cache_size = 0;
RESET QUERY CACHE;

... before any of this, otherwise your results will be meaningless.