which is better, creating a materialized view or a new table?

Question

I have some demanding mysql queries that have to select same frequently updated datasets from 5-7 mysql tables. 'Select' operation would be a bit more than CUD.

I am thinking of creating a table or materialized view to gather all demanding columns from other tables, so as to reduce overall query times to different tables and thus increase performance.

If I create that table, I may need to do extra insert / update / delete operation each time other tables updated.

if I create materialized view, I am worrying if the performance can be greatly improved. Because data from other tables are changing very frequently. Most likely, the view may need to be created first everytime before selecting it.

Any ideas? e.g. how to cache? other extra measures I can do?

Answer 1

I am thinking of creating a table or view to gather all demanding columns from other tables, so as to increase performance.
Most likely, the view may need to be created first everytime before selecting it.

Views are nothing but query. So doesn't matter whether you make query to select from view or just execute plain sql - the performance will be the same.

how to cache

Caching is very complex and specific question. So there is no panacea and to make decision more details should be provided.

Answer 2

It seems to me that you are thinking along the lines of the "materialized view" concept.

Mysql does not provide an implementation of this, though it can be simulated with some more or less sophistication (I do something similar to the later in Postgresql - it's handy for complex non-parametrized queries that are frequently used in reports, and for which is tolerable to have not totally up-to-date data).

Answer 3

As said below, there is no panacea for increasing performance. And, unlike what was said above, indexes are not the most important thing for DB performance - having a correctly set up database is. As the database gets larger DB configuration can come to dominate performance. Most important in this is having an appropriately configured disk subsystem since large databases are always limited in their performance by how fast data can be gotten to/from disk.

As for your specific questions, faking a materialized view via queries may or may not help you. It will likely slow down your insert and update performance while possibly increasing your select performance. Creating the "view" on demand when needed will do absolutely nothing for you as you're having to run your slow query to create it anyways. Since MySQL doesn't directly support materializing views a standard view will do nothing for you.

Without more details better help is impossible to give.