How to obtain mysql dependencies between views?

Question

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 1

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 feed the output to a python script that uses ete2 module to visualize the dependencies as a tree structure. I can share the script if anybody is interested. Edit: Caution, I just realized, this query may cause problems if a view name is a substring of another view name. If any body can suggest improvement, please do.

Answer 2

Edit: Caution, I just realized, this query may cause problems if a view name is a >substring of another view name. If any body can suggest improvement, please do.

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 % does eliminate your sub string problem and some other problems. In practice this will work. Only problem that I see is when you have a database where some table names are the same as column names, but in a proper DB design this won't be the case.

Answer 3

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.