I've been moved onto a new project which uses numerous VIEWS in MySQL (5.7) queries. From what I understand, Views in MySQL are not the most performant and shouldn't be used for the sake of saving a bit of typing. I'm trying to profile the performance so I have an idea of whether or not it's a bottleneck but I'm having trouble trying to unravel a view to it's underlying query.

I can see the SQL query for the view but then that SQL contains its own view lookup and there's a chain. Is there a way, ideally using MySQL WorkBench, to have the merged view outputted so I don't need to do it by hand? There can be chains of 4 or more views and my head is just confused trying to put the parentheses in the right places around the right SELECT...WHERE/JOIN queries and I'm sure there's a way to automate this.



I don't think there is a way to get the "merge", unless it is in the JSON; see below.

Meanwhile, run EXPLAIN SELECT ... and EXPLAIN FORMAT=JSON SELECT ... to see what it says. I think it will, with dubious clarity, tell you whether it is doing the "merge" or "materialization".

| improve this answer | |
  • Thanks for the reply. Unfortunately it's not there but there doesn't seem to be another way. Regardless, thanks for the FORMAT=JSON code, I haven't seen that before and could be useful for other purposes. – iso123 Feb 9 '17 at 9:48

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.