Блог

Главная - Вопросы по программированию - Создать индекс в запросе с меткой времени с помощью CTE

Создать индекс в запросе с меткой времени с помощью CTE

#mysql #indexing #common-table-expression

#mysql #индексирование #common-table-expression

Вопрос:

У меня есть следующий запрос:

 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 ;

Чтобы ускорить выполнение этого запроса, я создал индекс для GPSRecords3 :

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

и индекс для областей таблицы:

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

Мой вопрос, есть ли какие-либо другие способы заставить этот запрос выполняться еще быстрее? Нужно ли мне создавать какой-то другой индекс?

PS. Для 40 000 строк это занимает около 5 секунд.

РЕДАКТИРОВАТЬ 1

Это мое объяснение:

введите описание изображения здесь

Комментарии:

1. Пожалуйста, поделитесь EXPLAIN для этого запроса

2. Я отредактировал свой вопрос с помощью ОБЪЯСНЕНИЯ

3. У меня возникли проблемы с пониманием, почему у вас areas есть imei значения? Можете ли вы добавить CREATE TABLE как для areas , так и для GPSRecords3 таблицы? Вам нужны только 4 столбца в последнем выборе? b.areas_id, ANY_VALUE(b.is_in_or_out) is_in_or_out, MIN(b.timestamp) starttime, MAX(b.timestamp) endtime ?

4. 1 imei имеет 1 или более связанных областей. В GpsRecords3 данные вставляются в любую секунду из нескольких imeis. Мне нужно видеть каждую запись из GpsRecods3 в той области, с которой она связана, и это не в каждой временной метке. Что касается вашего вопроса о моем последнем выборе, мой ответ — нет. Я отредактировал свой вопрос с полным выбором.

5. Было бы проще, если бы вы могли предоставить результаты для SHOW CREATE TABLE GPSRecords3 и SHOW CREATE TABLE areas . (Я все еще не вижу необходимости в imei для областей, если только это не какая-то оптимизация, но вам нужно предположить, что если imei попадает в область, где он не связан, вы его не увидите. Это не имеет отношения к вашему вопросу, так что забудьте об этом).