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:

I am having trouble understanding why your `areas` have `imei` values? Can you add `CREATE TABLE` for both `areas` and `GPSRecords3` table ? Do you only need the 4 columns in the last select ? `b.areas_id, ANY_VALUE(b.is_in_or_out) is_in_or_out, MIN(b.timestamp) starttime, MAX(b.timestamp) endtime` ? — hsibboni, Sep 06 '20 at 00:58
1 imei has 1 or more areas associated. In GpsRecords3 data is inserted at any second from multiples imeis. I need to see each record from GpsRecods3 in wich associated area it is and it is not at every timestamp. Regarding your question about my last select my answear is no. I edited my question with the full select. — Codrut Rotaru, Sep 06 '20 at 05:15
It would be easier if you could provide the results for `SHOW CREATE TABLE GPSRecords3` and `SHOW CREATE TABLE areas`. (I still do not see the need for the imei on areas, unless it is some sort of optimization, but you need to assume that if an imei goes in an area where it is not linked, you will not see it. It is not relevant to your question, so forget it). — hsibboni, Sep 06 '20 at 08:21

Create index on timestamp query with CTE

0 Answers0