2

Running MariaDB 10.9.4 on Debian 11, I have a table of flights with approximately 50 million rows

My structure looks like:

id int(11) auto increment
flight_number varchar(16)
aircraft char(6)
airport_id int(6)
from_airport_id int(6)
arrival datetime
departure datetime

id is a primary key, and I have an index each on hex, arrival and airport_id (so, 3 indexes).

This table is written to every 30 minutes with a batch of approximately 5000 or so new rows. It is read from much more frequently than it is written to (the count queries below are sometimes being run up to 100 times a minute). Hence, I have chosen to run with the Aria engine.

The data is never deleted, though in future I plan to partition the table to have all records older than 365 days moved into its own separate partition.

I need to retrieve how many times a particular aircraft has been to a given airport_id in the last 7, 30, 90 and 365 days.

Initially, I queried my table along the lines of:

SELECT SUM(IF(arrival > '(current time - 7 days)', 1, 0)) AS `count_7d`, SUM(IF(arrival > '(current time - 30 days)', 1, 0)) AS `count_30d`, SUM(IF(arrival > '(current time - 90 days)', 1, 0)) AS `count_90d`, SUM(IF(arrival > '(current time - 365 days)', 1, 0)) AS `count_365d` FROM flights WHERE airport_id = 1 AND aircraft = 'ABCDEF' AND arrival > '(current time - 365 days)'

However, this was taking well in excess of 30 - sometimes even 60 seconds depending on server load - to query.

I instead started to query each field individually inside a function, to try and take advantage of the index on arrival a bit better.

SELECT COUNT(*) AS `count_7d` FROM flights WHERE airport_id = 1 AND aircraft = 'ABCDEF' AND arrival > '(current time - 7 days)';
SELECT COUNT(*) AS `count_30d` FROM flights WHERE airport_id = 1 AND aircraft = 'ABCDEF' AND arrival > '(current time - 30 days)';
...etc etc...

...this works a little quicker, each query is now taking about 5 seconds to run with it taking about 20 seconds to get all the data I need. The explain for this query shows that the index on each of the columns is being used.

Is there a better way of extracting all of the data I need within one query/a way of making the queries run in parallel (I understand Aria locks the table when selecting, but the counts do not need to be 100% accurate)? Would another database engine be more suitable to the task? Is there a composite index perhaps that might be suitable?

6
  • 1
    You need in one composite index, by (airport_id, aircraft, arrival) or by (aircraft, airport_id, arrival).
    – Akina
    Jan 3, 2023 at 20:57
  • 1
    Maybe you should share your table schema Jan 3, 2023 at 21:12
  • 1
    Aria reading a table doesn't lock the table on reads, only updates. If your batch is appending it might not even lock on that. If you are always getting 365 days I suspect your initial combined query would be best. '(current time - 7 days)' looks odd as string. Is DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) what you mean? (seems always true). SUM(IF(X, 1, 0)) can be simply SUM(X) where is is a boolean exprssion.
    – danblack
    Jan 3, 2023 at 21:48
  • Since you said you are using MariaDB and in fact using the Aria storage engine which doesn't exist in MySQL, I removed the mysql tag from this question. We need to start thinking of MariaDB and MySQL as different products, not compatible or flavors of the same product. You shouldn't tag a question both MySQL and MariaDB unless the question is about using both products. Jan 3, 2023 at 23:03
  • 1
    If you have 3-letter airport codes, why not use them instead of a 4-byte INT?
    – Rick James
    Jan 4, 2023 at 2:41

1 Answer 1

2

With help from Akina and danblack in the comments, I created a composite index on (airport_id, aircraft, arrival) which sped the individual queries up massively (now sub 1-second) and then went back to my original combined query, replacing the SUM(IF(X, 1, 0)) with just SUM(X) which brought the overall time to get the data needed down to around a second.

1
  • Glad to hear. That's about what I expected those changes to reach. Note, really only consider partitioning if you plan on purging off data or if you come by another select scenario where it gives a clear benefit.
    – danblack
    Jan 3, 2023 at 23:01

Your Answer

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

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