MySQL views and index usage

Question

I was thinking of starting to use views to reduce the complexity of the code and queries in our project - some of them have several unions, and from what I understand, MySQL views will allow us to simplify the link to this data a little in several places.

There are many different things where "MySQL does not use indexes for views", "You cannot have an indexed view", "Only if you use MERGE" ... There is no clear cut out answer.

So, to abort the chase: Do MySQL views use indexes for the tables from which they are built? Is it good to use views at all because performance will be terrible or will it use indexes on base tables when performing its joins? If I sort the view by the column indexed in the table, will it still sort as fast as usual?

My research seems to indicate that views do not use indexes, but if so, no one has ever used them; obviously people do it, so ...?

Sorry if this seems absurd.

Answer 1

If you request a view, MySQL will consider using indexes in base tables.

However, it is not possible to add a new index to the computed column in the view. I think this means that MySQL did not have indexed views, unlike (for example) SQL Server indexed views .

Answer 2

Do MySQL views use indexes in the tables from which they are built?

Yes.

What people possibly refer to when they say that MySQL doesn't use indexes for views has something called materialized views or indexed views , in which the actual view is physically stored in the file system as a regular table. Indexes can be created for these views in some DBMSs, such as Oracle or SQL Server. Essentially, indexed views become a copy of the source tables that make it up, and kept in sync automatically , sort of.

Read this article on Indexed Views on SQL Server, for example.