Performance considerations when using MySQL VIEWs

Question

I was considering using MySQL views to provide an abstraction when pulling data from the DB. As I was looking for material on this, I came across this article, which ends with:

MySQL has long way to go getting queries with VIEWs properly optimized.

The article is from 2007. Is this still applicable? Eg: Has MySQL solved these issues?

Answer 1

MySQL views work fine functionally, but they perform badly in the majority of cases.

This is because introducing even quite a simple view tends to cause a much worse query plan to be used by the optimiser. This makes using views impractical in the general case.

Often the server will materialise the entire view as a temporary table, which is not helpful for good performance (unless it happens to have only a very small number of rows in it).

So if you thought the explain plan was ok without a view, with a view it can turn terrible. This is still unresolved in the latest dev version of MySQL as far as I know.

You can have views, but don't expect decent (i.e. acceptable) performance.