Is it possible to have an indexed view in MySQL?

Question

I found a post on MySQL forums since 2005 , but nothing more recent. Based on this, it is impossible. But much can change in 3-4 years.

What I'm looking for is a way to have an index over the view, but the table being viewed remains unindexed. Indexing harms the writing process, and this table is written quite often (to the point that indexing slows things down to a crawl). However, this lack of index makes my queries painfully slow.

Answer 1

I do not think MySQL supports the materialized views that you need, but in any case, this will not help you in this situation. Regardless of whether the pointer is in the view or in the base table, it will need to be written and updated at some point during the update of the base table, so this will still cause problems with the write speed.

It is best to create pivot tables that are periodically updated.

Answer 2

Have you considered abstracting transaction processing data from analytic processing data so that it can be specialized to meet your unique requirements?

The main idea is that you have one version of the data that changes regularly, this will be the transaction processing side and requires intensive normalization and light indexes to write operations quickly. The second version of the data is structured for analytical processing and, as a rule, is less normalized and more strongly indexed for quick reporting.

Data structured around analytical processing is usually built around a data warehouse cube methodology consisting of fact tables that represent the sides of the cube and dimension tables that represent the edges of the cube.

Answer 3

Flexviews supports materialized views in MySQL by tracking changes to base tables and updating a table that functions as a materialized view. This approach means that the SQL supported by the view is a bit limited (since change logging procedures should determine in which tables it should track changes), but as far as I know, this is the closest access to materialized views in MySQL.

Answer 4

Do you need only one indexed view? It is unlikely that writing to a table with a single index would be destructive. Is there a primary key?

If each entry is large, you can increase productivity by figuring out how to reduce it. Or shorten the length of the required index.

If this is a table with a record (i.e. you do not need to make updates), in MySQL it can be deadly to start archiving it or otherwise delete records (and index keys), requiring the index to start filling out (reusing) slots from deleted keys, not just adding new index values. Consistent, but in this case you are better off with a large table.