SQL query from multiple ARCHIVE tables with common ORDER BY?

Question

MySQL

Greetings! I'm trying to decide on the future scheme for storing logs of user actions, I looked at ARCHIVE and a question arose from the subject. There will be 6 ARCHIVE tables each with a date in the title (the last 6 months), so how can I then select from them to view the log, so that the ORDER BY is for all data from all 6 tables? Or just specifying the same ORDER BY in all 6 queries with UNION will work as I expect? And wouldn't such a selection be too slow? 1-10 million entries per month (in each table). Or is it better then to use MyISAM so that there are indexes and the selection goes faster, but then the volume will be about 10 GB in 6 months.

Answer 1

Acrhive base is always a full table scan. But they are very fast to add. Any ORDER BY expression on such a table is a merge sort with dumping heaps of data into temporary files (essentially building an index on the fly), which means it takes a long time. UNION ALL will not sort multiple selections at once, it will simply join the results. On the other hand, all of your tables contain data from only one interval, which means that all records in a table with a higher date will be older than records in any other lower table. Then just UINION ALL of all tables in date order will give you the result you want.

Answer 2

If the data will not be modified, then you can try ALTER TABLE ORDER BY. Then all you have to do is union and put the tables in the correct order