How to get mysql dependencies between views?

Question

I have a mysql database with over 60 views, some helper and some final. They have dependencies between them. Of course, this gives me performance problems, but I did not design this database and system.

To speed up some reports, I materialize the latest views in the tables. I could accelerate this process by materializing auxiliary representations, and then using them to materialize others, eliminating the need for reprocessing auxiliary ones.

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

Is there a way to do this, besides manually analyzing each species?

Answer 1

A single view request can be visualized in many query designers, but the structure of all views in the database remains unobservable.

• MySQL Workbench and Oracle SQL Developer seem to display views as separate objects.
• SchemaSpy displays a graph graph that is based on field names and not in the FROM clause.

Unable to manage ~ 50 interconnected views. I have not yet found a convenient tool for this task.

Answer 2

I used the following query to get the dependencies from the FROM clause:

SELECT views.TABLE_NAME As `View`, tab.TABLE_NAME AS `Input` FROM information_schema.`TABLES` AS tab INNER JOIN information_schema.VIEWS AS views ON views.VIEW_DEFINITION LIKE CONCAT('%',tab.TABLE_NAME,'%') 

Then I pass the output to a python script that uses the ete2 module to render the dependencies in a tree structure. I can share the script if anyone is interested. Edit: Caution, I just realized this query can cause problems if the view name is a substring of another view name. If any body can suggest an improvement, do it.

Answer 3

Edit: caution, I just realized this query can cause problems if the view name is> a substring of another view name. If any body can suggest an improvement, do it.

 SELECT views.TABLE_NAME As `View`, tab.TABLE_NAME AS `Input` FROM information_schema.`TABLES` AS tab INNER JOIN information_schema.VIEWS AS views ON views.VIEW_DEFINITION LIKE CONCAT('%`',tab.TABLE_NAME,'`%') 

I think the `signs next to% really fix your substring problem and some other problems. In practice, this will work. The only problem that I see is when you have a database where some table names match the names of the columns, but in the correct database structure this is not so.