0

We have a web application that uses MariaDB relational mode using tables with "InnoDB" engine, because our application inserts and updates many data each second. Also, we have a table to load those data inserted or updated.

The table contains about 40M records and has about 140.000 records daily on production environment, so it will increase its size considerably. Also it deletes 3 years old records. The data I provide is with about 8M records on a test environment.

The query I execute is:

SELECT d0_.id                        AS id_0,
       d0_.plate                     AS plate_1,
       d0_.original_plate            AS original_plate_2,
       d0_.datetime_utc              AS datetime_utc_3,
       d1_.way_type_name             AS way_type_name_4,
       d1_.name                      AS name_5,
       d1_.number                    AS number_6,
       d0_.lists_names               AS lists_names_7,
       d0_.is_in_blacklist           AS is_in_blacklist_8,
       d0_.is_in_whitelist           AS is_in_whitelist_9,
       d0_.atex_alarm_type           AS atex_alarm_type_10,
       d2_.name                      AS name_11,
       d2_.guid                      AS guid_12,
       c3_.name                      AS name_13,
       c4_.name                      AS name_14,
       d2_.serialnumber              AS serialnumber_15,
       d2_.cinemometer_serial_number AS cinemometer_serial_number_16,
       d0_.lane                      AS lane_17,
       c5_.name                      AS name_18,
       d0_.detect_dir                AS detect_dir_19,
       d0_.fiability                 AS fiability_20,
       d0_.char_fiability            AS char_fiability_21,
       d6_.road_limit                AS road_limit_22,
       d6_.photo_limit_tur           AS photo_limit_tur_23,
       d0_.speed                     AS speed_24,
       d6_.speed_applied             AS speed_applied_25,
       d6_.vehicle_classification    AS vehicle_classification_26,
       v7_.name                      AS name_27,
       v8_.name                      AS name_28,
       v9_.name                      AS name_29,
       v10_.name                     AS name_30,
       d0_.session_identifier        AS session_identifier_31,
       a11_.description              AS description_32,
       a12_.description              AS description_33,
       a13_.description              AS description_34,
       d6_.is_delito                 AS is_delito_35,
       d1_.limitation_type_name      AS limitation_type_name_36,
       d1_.type_name                 AS type_name_37,
       d1_.direction                 AS direction_38,
       d1_.place                     AS place_39,
       d1_.province                  AS province_40,
       d6_.vehicle_distance          AS vehicle_distance_41,
       d14_.type                     AS type_42,
       d14_.latitude                 AS latitude_43,
       d14_.longitude                AS longitude_44,
       d14_.num_satellites           AS num_satellites_45,
       d0_.img_mask_name             AS img_mask_name_46,
       d15_.id                       AS id_47
FROM detection d0_ USE INDEX (index_datetime)
         LEFT JOIN vehicle_type v7_ ON d0_.vehicle_type_id = v7_.id
         LEFT JOIN vehicle_model v9_ ON d0_.vehicle_model_id = v9_.id
         LEFT JOIN vehicle_brand v8_ ON v9_.brand_id = v8_.id
         LEFT JOIN vehicle_color v10_ ON d0_.color_id = v10_.id
         LEFT JOIN device d2_ ON d0_.device_id = d2_.id
         LEFT JOIN camera_model c4_ ON d2_.camera_model_id = c4_.id
         LEFT JOIN camera_brand c3_ ON c4_.camera_brand_id = c3_.id
         LEFT JOIN country c5_ ON d0_.country_id = c5_.id
         LEFT JOIN detection_location d1_ ON d0_.location_id = d1_.id
         LEFT JOIN detection_trucam d6_ ON d0_.trucam_data_id = d6_.id
         LEFT JOIN awm_detection_status a11_ ON d0_.detection_status_id = a11_.id
         LEFT JOIN awm_operation_type a13_ ON d0_.operation_type_id = a13_.id
         LEFT JOIN awm_operation_mode a12_ ON a13_.operation_mode_id = a12_.id
         LEFT JOIN detection_gps d14_ ON d0_.gps_data_id = d14_.id
         LEFT JOIN detection_zbe_amb d15_ ON d0_.amb_infraction_id = d15_.id
WHERE (d0_.datetime_utc BETWEEN '2021-11-01 23:00:00' AND '2021-11-16 22:59:59')
  AND d0_.original_plate LIKE '%8950%'
LIMIT 25;

We have some index created, but the query takes about 20 seconds to execute:

Index table

The explain table shows:

+------+-------------+-------+--------+----------------+----------------+---------+------------------------------------+---------+--------------------------------------------------------+
| id   | select_type | table | type   | possible_keys  | key            | key_len | ref                                | rows    | Extra                                                  |
+------+-------------+-------+--------+----------------+----------------+---------+------------------------------------+---------+--------------------------------------------------------+
|    1 | SIMPLE      | d0_   | range  | INDEX_DATETIME | INDEX_DATETIME | 5       | NULL                               | 2880236 | Using index condition; Using where                     |
|    1 | SIMPLE      | v7_   | ALL    | NULL           | NULL           | NULL    | NULL                               | 47      | Using where; Using join buffer (flat, BNL join)        |
|    1 | SIMPLE      | v9_   | ALL    | NULL           | NULL           | NULL    | NULL                               | 500     | Using where; Using join buffer (incremental, BNL join) |
|    1 | SIMPLE      | v8_   | ALL    | NULL           | NULL           | NULL    | NULL                               | 154     | Using where; Using join buffer (incremental, BNL join) |
|    1 | SIMPLE      | v10_  | ALL    | NULL           | NULL           | NULL    | NULL                               | 30      | Using where; Using join buffer (incremental, BNL join) |
|    1 | SIMPLE      | d2_   | ALL    | NULL           | NULL           | NULL    | NULL                               | 41      | Using where; Using join buffer (incremental, BNL join) |
|    1 | SIMPLE      | c4_   | eq_ref | PRIMARY        | PRIMARY        | 4       | lprmanager.d2_.camera_model_id     | 1       | Using where                                            |
|    1 | SIMPLE      | c3_   | eq_ref | PRIMARY        | PRIMARY        | 4       | lprmanager.c4_.camera_brand_id     | 1       | Using where                                            |
|    1 | SIMPLE      | c5_   | eq_ref | PRIMARY        | PRIMARY        | 4       | lprmanager.d0_.country_id          | 1       | Using where                                            |
|    1 | SIMPLE      | d1_   | ALL    | NULL           | NULL           | NULL    | NULL                               | 1       | Using where; Using join buffer (flat, BNL join)        |
|    1 | SIMPLE      | d6_   | ALL    | NULL           | NULL           | NULL    | NULL                               | 1       | Using where; Using join buffer (incremental, BNL join) |
|    1 | SIMPLE      | a11_  | eq_ref | PRIMARY        | PRIMARY        | 4       | lprmanager.d0_.detection_status_id | 1       | Using where                                            |
|    1 | SIMPLE      | a13_  | eq_ref | PRIMARY        | PRIMARY        | 4       | lprmanager.d0_.operation_type_id   | 1       | Using where                                            |
|    1 | SIMPLE      | a12_  | eq_ref | PRIMARY        | PRIMARY        | 4       | lprmanager.a13_.operation_mode_id  | 1       | Using where                                            |
|    1 | SIMPLE      | d14_  | ALL    | NULL           | NULL           | NULL    | NULL                               | 1       | Using where; Using join buffer (flat, BNL join)        |
|    1 | SIMPLE      | d15_  | ALL    | NULL           | NULL           | NULL    | NULL                               | 1       | Using where; Using join buffer (incremental, BNL join) |
+------+-------------+-------+--------+----------------+----------------+---------+------------------------------------+---------+--------------------------------------------------------+
16 rows in set (0.001 sec)

I have tested many solutions, including FULLTEXT INDEX (could not be used with DATETIME INDEX), Adding and removing index, but I have no more ideas about how to optimize the performance of the query.

Thank you in advance. Best regards.

5
  • 1
    An index on (datetime_utc, original_plate) could be useful, you seem to have the inverse. Also, to be able to help you better, DDL for the tables involved would be useful, but not in image format. What is the primary key of eg. vehicle_type? Dec 2, 2021 at 19:55
  • 1
    Have the vehicle tables ever been analyzed? Dec 2, 2021 at 20:12
  • 1
    I will test to create the Index in the inverse order. Thanks
    – dual
    Dec 2, 2021 at 21:14
  • 1
    What version of MySQL/MariaDB?
    – Rick James
    Dec 3, 2021 at 0:02
  • MariaDB 10.4.18, thanks
    – dual
    Dec 5, 2021 at 11:07

1 Answer 1

0

This may help:

SELECT ...
    FROM ( SELECT id FROM d0_
            WHERE d0_.datetime_utc >= '2021-11-01 23:00:00'
              AND d0_.datetime_utc  < '2021-11-01 23:00:00' + INTERVAL 15 DAY
              AND d0_.original_plate LIKE '%8950%'
            LIMIT 25
         ) AS a
    LEFT JOIN next_table ON ...
    LEFT JOIN next_table ON ...
    ... ;

The idea is to find the 25 rows, then do all the JOINing.

If plate is a single word, don't use FULLTEXT to index it. A regular index would be better.

it deletes 3 years old records

I recommend PARTITION BY RANGE (TO_DATE(...)) and have about 38 monthly partitions. More discussion: http://mysql.rjweb.org/doc.php/partitionmaint

Schema comments:

  • Lat/lng should not be in DOUBLE.
  • BIGINT is usually bigger than necessary.
  • On the other hand, the main id for this table may eventually exceed 2 or 4 billion.
  • The standard 2-letter country_codes is better than reaching into a separate table.
  • An "array" of 4 names sounds 'wrong'. Ditto for 3 "description" columns.
  • TIMESTAMP may be better than DATETIME for UTC -- Consider the mess that DST causes.

LIMIT without ORDER BY -- you won't get a predictable set of rows.

If you will be paginating, there are other issues to consider.

Do you really produce a list of 48 columns?

200M rows in the resulting table is "big", but not "huge. Still, you should consider shrinking datatypes where practical.

1
  • Thank you for your anwer. I will try the solution you provide. Otherwise, I have optimized the queries with a multiple column index including (datetime_utc, original_plate, plate, device_id and country_id). I couldn't include standard 2-letter-country-codes because I need the table to convert countries code between many cameras manufacters and ISO codes. Also,I temporary remove ORDER BY to test the performance, but with the multiple column index it works well with ORDER BY. And finally, I really produce a list of 48 columns (the table contains about 140 columns for other purposes).
    – dual
    Dec 5, 2021 at 11:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.