Mysql – Does using a view over standard queries and table indexes improve performance on large databases

indexMySQLperformanceperformance-tuningquery-performanceview

I recently had a discussion with someone who said that using a view on a database with a large number of tables and (>1,000000) rows would allow for much better performance.

I don't see how this would be the case as a view is just a stored query definition as far as I am aware.

This was in relation to a web based app which allows for the filtering of products based on type, brand, spec, etc… and needs to be very performant.

Given that queries are effectively hard coded into the app, its not like a person needs to create each query manually so I'm thinking that there should be no difference between using a view or running a standard query againts the needed tables that uses joins…

I had suggested that using indexes on the tables would allow for the performance needed but they said that when tables have more than around 200k rows, indexes are useless in terms of speeding things up…

Is this correct about table size removing the effectiveness of indexes and are there any performance increases to be had from using views over standard queries?

Any help will be much appreciated.

Best Answer

In general, I would say that you are right. Views are nothing more than a query stored in the catalogue of the DBMS.

Certain types of views (Materialized Query Tables, MQT) can materialize rows on disk but they have problems of there own (the cost of keeping them up to date). They are typically used for data warehouses and not so much for heavy OLTP due to the cost of maintaining them. This is a new concept in MySQL that I was not aware of. @Verace pointed me to the following links in his comment:

Another thing that comes to mind is statistical views. At least Db2 have this possibility. In short, you enable a view to become a statistical view, and then you can update statistics for that view. It can be a great help for the optimizer to have statistics for a join instead of using a formula. I don't think this concept exists in MySQL.

With these minor remarks I would say that it's definitely wrong to claim that views in general "allow for much better performance.".

When it comes indexes, I must say that this claim is probably one of the weirdest ones I've heard. Using an index to access a row is O(log n) complexity compared to O(n) for accessing the row directly in the table. Therefore the benefit of an index grows as the table grows in size, it does not become useless when the table reaches some particular size.