A
A
Andrey Strelkov2019-02-01 15:19:58
MariaDB
Andrey Strelkov, 2019-02-01 15:19:58

What is the correct way to create the idea of ​​a "materialized view" in MySQL/MariaDB?

Good afternoon, there is a MariaDB database, inside which there are many databases of varying complexity, each database is responsible for its own kind of information, let's take it as a fact that, due to the complexity of the databases and information, the created views display this or that information, but the speed representations are heavy , some take 5 seconds, others 10, etc. It seems like it’s not critical to create some kind of reporting, but now there is a task that you need to quickly display certain information from views, and give a search for it.
Only the simplest solution comes to mind, it is to schedule a copy of these views to a table
And here, rather, the question is more how to do it better, how mysql works. Namely, if, for example, one view is executed for 30 seconds, i.e. I want to have a table that already has a structure, first TRUNCATE then INSERT values ​​from the view, at this moment, when this long copy of data is going on, how does mysql feel, it calmly accepts other queries, and it doesn’t feel particularly difficult (i.e. speed the work of other requests is noticeable, or does not even fall at all, the only thing is that one connection takes a long time)
And immediately the question is, if I have, for example, such 5 databases, each with 5 views, it’s probably not at all right to shell them all at once, which is more correct , create some one procedure, which in turn will be TRUNCATE + INSERT, TRUNCATE + INSERT, ...
Or also, depending on the required relevance of the data, call each procedure something like EVERY_HOUR, EVERY_DAY and place the necessary views there.
The question is more interested in how the database feels in such launches.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question