35

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.

Xkeeper
  • 995
  • 3
  • 11
  • 22

2 Answers2

34

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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • I was starting to believe that was the case, but wanted to make absolutely sure before I committed to anything. Thanks a ton. – Xkeeper Oct 27 '11 at 22:16
18

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.

Icarus
  • 63,293
  • 14
  • 100
  • 115
  • I see... I wish I could accept both of your answers because they're both very, very good. On that note, it seems like one could psuedo-gimp a solution by having an INSERT/UPDATE-based trigger create a table based off of the view (possibly?), but that would be such a performance hit as to be useless outside of an archived database. – Xkeeper Oct 27 '11 at 22:17