-1

I have a table with a lot of records, and I want to restrict the data access to the last 3 years only, but I still want to keep the data there.

I saw a solution where is it possible to create an indexed view that restrict this data using a date filter, and this view becomes the main entrypoint to query the records, for example:

  • I have a user_log table with millions of records
  • I rename this user_log to user_log_history
  • I create a view named user_log that filters the user_log_history table from the last 3 years.

I saw that some databases allow you to insert/update/delete through views if you follow a set of rules, which looks ok in my case.

So my question is: are there any performance downside on doing this? I'm using MySQL by the way.

Improve this question
1
  • Describe further your queries. It may be useful to use partitioning and/or summary tables. – Rick James Nov 5 '20 at 15:37
-1

The biggest downside is that a view can not use INDEXES.

see https://dev.mysql.com/doc/refman/8.0/en/view-restrictions.html

On big tables, you have hen performance issues.

You can take a look at materialized views, but they are not native to MySQL, so have to be programmned.

Improve this answer
6
  • 1
    But wouldn't the view's query use the indexes on the underlying tables? – a_horse_with_no_name Nov 5 '20 at 12:50
  • in mysql. you see views very rarely, because of the major drawbacks, and there are over the years no progress(and not expected in future) it is a thing like right join, it exits but it is rarekly used. – nbk Nov 5 '20 at 12:59
  • 1
    Views can use indexes. In some cases. Say if definition is CREATE VIEW v AS SELECT c1, c5, c7 FROM t; where c1 is PK and (c5,c7) is a multicolumn index, then both indexes will be inherited by the view. View is just an alias for underlying query and each reference to the view is translated to the subselect by optimizer. – Kondybas Nov 5 '20 at 17:20
  • 1
    I strongly disagree with this answer. The above documentation is in regards to the restriction of creating & using indexes directly on top of the view itself. But views in MySQL can and do use indexes of their underlying tables in certain cases. It does sound like it's more limited than other enterprise level RDBMS like SQL Server or Oracle PL/SQL because predicates don't get pushed down. Please see this StackOverflow post showing how MySQL views will use covering indexes of their underlying tables: stackoverflow.com/questions/13944946/… – J.D. Nov 5 '20 at 19:03
  • 1
    After re-reading OPs question, he does specifically ask about "indexed views" which is a concept different from a view using it's underlying table's indexes. So I think there's a little confusion in this comments section. This answer is technically true in regards to "indexed views" but in regards to the main general question in OP's title, there shouldn't be any necessary drawbacks of using a view with a filter, and you might be able to get it to use the indexes of the underlying tables it references depending on how your indexes are setup, as mentioned in the StackOverflow post I linked. – J.D. Nov 5 '20 at 19:09

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.