CODEWITHSUNDEEP
mysqlview
Xkeeper
Xkeeper

Reputation: 1085

MySQL Views and index use

I was thinking of starting to utilize views to reduce the complexity of code and queries in our project -- some of them have a few joins, and from what I understand, MySQL views would allow us to reference that data a little easier in multiple places.

There's a lot of stuff being thrown around, where "MySQL doesn't use indexes for views", "You can't have an indexed view", "Only if you use MERGE"... There is no clear-cut answer.

So, to cut to the chase: Do MySQL views use indexes on the tables they are built from? Is it a bad idea to use views at all because performance will be abysmal, or will it use the indexes on the underlying tables when doing its joins? If I sort a view by a column that is indexed in the table, does it still sort as fast as it normally would?

Doing my research seems to indicate that views don't use indexes, but if that was the case nobody would ever use them; obviously people do, so...?

Sorry if this seems kind of absurd.

Upvotes: 35

Views: 24985

Answers (2)

Icarus
Icarus

Reputation: 63966

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

Yes.

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

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

Upvotes: 18

Mark Byers
Mark Byers

Reputation: 838566

If you query a view, MySQL will consider using indexes on the underlying tables.

However it is not possible to add a new index to a calculated column in the view. I think this is what people meant by MySQL not having indexed views, as opposed to (for example) SQL Server's indexed views.

Upvotes: 34

Related Questions