miguelcobain miguelcobain - 27 days ago 9
MySQL Question

How to obtain mysql dependencies between views?

I have a mysql database with more than 60 views, some auxiliary and some final. They have dependencies between them. This is giving me performance issues, of course, but I didn't design this database and system.

To speed up some reports I'm materializing the final views into tables. I could speed up this process by materializing the auxiliary views too, and then using them to materialize the others, sparing the re-processing of the auxiliary ones.

To do this I need a way to see the dependencies between views, so I can materialize the views in the correct order. It would be great if I could input that information to a graph (using tools such as Graphviz or Tikz, for example).

Is there any way to do this besides manually analysing each view?

Answer

A separate view's query can be visualized in many query designers, but the whole views' structure in database stays unobservable.

  • MySQL Workbench and Oracle SQL Developer seem to render views as separate objects.
  • SchemaSpy renders views graph, which is based on field names, not FROM clause.

It's hard to manage ~50 interconnected views. I haven't found any comfort tool for that task yet.