Index views in Mysql

Asked

Viewed 1,453 times

0

I have a view in my Mysql database, I would like to know if it is possible to index the fields that are contained in it, because I have a group by on it that is consuming much, and a index would solve that problem or at least help.

Is there any way to do this index in view in Mysql?

mysql sql database view índices

  • Did the answer solve what you were looking to know? Do you think you can accept it now? If not, you need something else to be improved?

2 answers

4

It is not possible to do anything specific like this. If the view is not meeting the performance requirements has three outputs:

  • Do not use the view, access the data directly from the tables, you may discover that the problem wasn’t even in the view and have to fix the problem that was further down.
  • Create indexes that help the searches to be efficient in the physical table, after all that is what matters in fact, indexes are optimizations to access the real data, not logical data, which is the function of view. To view benefit if the physical scale benefits.
  • Create an auxiliary table with the data you would get from view (its existence even facilitates this, is a insert/select very simple) and index it. I know this is not ideal, but it is still an alternative.

  • I currently use the normal query, I thought to migrate to the view, to index these fields, but I see that this is not possible so my problem to index the fields is that the fields of group by are of different tables, they are indexed already inside their tables, but with the joins of the query they do not have the expected performance, needed an index with the 3 fields together, however the 3 are of different tables. In my case is for an Ecommerce, where I have your stock products and attributes, and the query for listing, have to display the product blue color, separate from the green color...

  • which are the same product but of different attributes, in this case different colors. And the problem is that the more products my query returns the slower it gets due to reading the database to group the data.

  • And it should not be possible to re-model the physical tables to avoid this too, right?

  • Not because it has to be something that caters to different products, so it has to be something kind of generic, but this index is slowing down, some filtered categories, while a category that has about 200 products loads in 0.5 seconds with another 1000 takes 3 seconds, even both being with limit 0.15, due to the bank scan before the limit

  • And my problem of creating an auxiliary table, would be that my query brings the values of the products, so there are promotions that are active, free shipping...

1

To use View Indexes simply update Mysql to version higher than 5.6.3, from this version Mysql uses the index derived from the physical table.

Browser other questions tagged mysql sql database view índices

You are not signed in. Login or sign up in order to post.