1

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.

  • 2
    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... I'm with @Lennart on this - complete and utter garbage! Send your colleague here! – Vérace May 20 '18 at 11:15
  • Thank you Verace, I thought as much, the person I spoke to was adamant that using views would solve the problem of split second performance when using a front end web app to filter a large dataset to display a specific products information on a webpage from a backend storing 10s or 100s of thousands of products and their related info... They said indexes wouldn't work as there would be too many records... – Mark Smith May 20 '18 at 11:39
  • 3
    That "indexes wouldn't work as there would be too many records" is even worse. Just don't give any credit to whatever that oerson says about database performance. – ypercubeᵀᴹ May 20 '18 at 12:29
  • 1
    What is this person's function within your organisation? – Vérace May 20 '18 at 17:29
  • They are an external consultant - apparently they have successfully completed many web projects and he seems to feel he really knows how to make data-driven websites respond very quickly... I thought something was fishy when he said that simply using a view to grab the data would make things run more quickly... – Mark Smith May 20 '18 at 18:15
3

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.

  • The concept of materialized views exists (err... sort of :-) ) in MySQL - see here and here! – Vérace May 20 '18 at 10:29
  • @Verace, Interesting I did not know that. At a first glance, it looks a bit less elegant than other vendors implementation. – Lennart May 20 '18 at 10:36
  • Thanks for all your comments, they are useful, I'm afraid I can't upvote any of them as my score is too low... – Mark Smith May 20 '18 at 11:14
0
  • MySQL's VIEWs are just syntactic sugar; no performance benefit over writing the equivalent SELECT.

  • INDEXes, where appropriate, are excellent at improving performance of large, or even huge, tables. Indexes are essentially mandatory for billion-row tables.

  • Show us an Index that proves the consultant's point; we will explain how he messed it up (datatype, collation, cardinality, etc).

  • MySQL does not implement "Materialized Views", but there are good reasons, especially in Data Warehouse applications, to build and maintain such. My doc on such.

  • A "large number of tables" with very similar schemas is usually (not always) a bad design.

  • If you have a table with a lot of "products" that have a large number of "attributes" (size, color, manufacturer, etc), searching is difficult. It is not practical to add enough indexes for efficiency; the queries are too varied; etc. Entity-Attribute-Value is a tempting model, but if falls apart even before 200K products. My discussion and partial solution .

(I agree with other Comments that you are listening to the wrong so-called DBA.)

If you want to dig deeper about your particular tables and application, please provide more details. (And I suggest starting a new Question; this Question has turned into a generic flame war on whether Indexes work.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.