Create index on timestamp query with CTE

Question

I have the following query:

WITH cte1 AS
(
    SELECT
        g.id,
        g.IMEI,
        Timestamp,
        GPS_Latitude,
        GPS_Longitude,
        CASE WHEN GPS_Speed > 0 THEN 1 ELSE 0 END IO_Engine,
        IO_Kilometers,
        IO_Fuel,
        a.areas_id, 
        CASE 
           WHEN a.type = 'polygon' 
              THEN ST_CONTAINS(
                      ST_GEOMFROMTEXT(concat('POLYGON((',a.coords,'))'),4326), 
                      ST_GEOMFROMTEXT(concat("POINT(",GPS_Latitude," ",GPS_Longitude,")"),4326)) 
              ELSE 
                 CASE WHEN
                      ST_Distance(ST_GeomFromText(concat('POINT(',GPS_Latitude," ",GPS_Longitude,')'), 4326),
                                  ST_GeomFromText(concat('POINT(',SPLIT_STRING(a.coords, ',', 1),')'), 4326)) <= cast(SPLIT_STRING(a.coords, ',', 2) as float) 
                                       then 1 else 0 end 
                 end is_in_or_out
       from GPSRecords3 g join areas a on g.imei=a.imei  
       where a.imei='352093081706706' and 
             date(timestamp)='2020-08-7' and 
             timestamp >= '2020-08-7 7:00' and 
             timestamp<= '2020-08-7 19:00' and 
             a.areas_id in(22,23,24,25,26,27)) ,

cte2 AS (SELECT timestamp,
                areas_id,
                IO_Engine,
                IO_Kilometers,
                IO_Fuel,
                is_in_or_out,
                CAST(ROW_NUMBER() OVER (PARTITION BY areas_id ORDER BY timestamp ASC) AS SIGNED)
                 -CAST(ROW_NUMBER() OVER (PARTITION BY areas_id ORDER BY is_in_or_out, timestamp ASC) AS  SIGNED) AS grp
         FROM cte1 a
)
SELECT 
    b.areas_id, 
    ANY_VALUE(b.is_in_or_out) is_in_or_out,
    MIN(b.timestamp) starttime,
    MAX(b.timestamp) endtime,
    MAX(IO_Kilometers) endkm,
    MIN(IO_Kilometers) startkm,
    MAX(IO_Fuel) endfuel,
    MIN(IO_Fuel) startfuel
FROM 
    cte2 b
GROUP BY 
    areas_id, grp
ORDER BY 
    starttime, areas_id ;

In order to make this query run faster I created an index for GPSRecords3 :

 ALTER TABLE GPSRecords3 
     ADD INDEX ix_ts2 ((date(Timestamp)) ASC, imei) USING BTREE;

and an index for table areas:

 ALTER TABLE areas 
     ADD INDEX ix_imeicoord (imei) USING BTREE;

My question is there some other ways to make this query run even faster? Do I need to create some other index?

PS. For 40 000 rows it takes about 5 sec.

EDIT 1

This is my explain statement:

Create index on timestamp query with CTE

0 Answers