Alternative to Temporary Table with Create View - MySQL

Question

I have a view which uses many tables, and statements and functions. It is very slow, the main reason being that I only have read-only access to the main tables which do not have the appropriate indexes, so joins evaluate very slowly.

Following some advice I have broken the joins down using temporary tables, to ensure that every join has indexed fields on both sides. This has worked very well. If I run a select to pull the final output it runs 60x quicker than previously. However I cannot use this to drive the necessary view, as views cannot refer to temporary tables.

Are there any alternatives to temporary tables, or different approaches I could take?

Thanks

Answer 1

View is just an alias to the query that allow you to use its result in other queries in terms of tables not subqueries. But each time you refer to the view, the query from its definition is invoked. No temporary tables created when view is defined.

Therefore views do not improve the overall performance at all and are intended to make DB structure more clear and logical.

Prior to the MySQL 5.7 query results can be cached that really speed up some queries. But as far as caching conflicts with InnoDB internals, it is disabled now and can cause significant slowdown and overall performance losses when enabled. So views should be considered as threat to the performance.

On the other hand, temporary tables, especially those with ENGINE=MEMORY, can significantly improve the performance. But you have to ensure that temptable is updated each time the referred tables are updated. Triggers are the good tool to keep reference consistency of the derived tables. Sure even memory-based temptables should be properly indexed for maximum performance.

Answer 2

Write a stored procedure to do the task. It can end with (approximately) the SELECT that 'should be' in the VIEW.