4 ms·

Just so folks know, there are tools that will decompose queries and make nice little pictures out of them. With something like this, you'd have to use it just t

Just so folks know, there are tools that will decompose queries and make nice little pictures out of them. With something like this, you'd have to use it just to get started. Once you've visually decomposed it, you'd physically decompose it by splitting it inside-out. Then proceed to understand and debug inside-outwards. Not fun, but not impossible. Just a huge pain in the ass. Making it more fun would be a database with bad RI, nulls, and duplicate data all over the place. Don't get me wrong -- from looking at the image it definitely looks like aspirin will be required. :)

offbyone 15y ago
I've always believed those tools must exist, but sorting out the SEO crap and advertising copy that gloms up search results for them is painful. Can you name any Linux/Mac tools like that?
- DanielBMarkham 15y ago
  Not linux, but I have a windows example. Being an El Cheapo, what I do from a windows box is fire up MS Access, link into the database (and this works even if the database is on a *nix box somewhere), then drag the text of the query into the graphical query builder tool. It's taken some pretty complex stuff apart for me in the past. It's been a while since I last deep-dove in a complex database, so I don't have any other examples handy. Sorry. Maybe somebody else can pick up the thread. I remember reviewing a bunch of them several years ago -- these tools have been around for a long time.
- myth_drannon 15y ago
  on PostgreSQL it's part of pgAdmin you don't need a separate tool
- shrike 15y ago
  I haven't found anything for Mac/Linux to do it but on Windows Visio can actually do a good job of visualizing a schema and SP structure.
waqf 15y ago
I want to see someone use a language model for SQL to reconstruct the query just from that photograph, and then debug it.
dmk23 15y ago
I use the cheapest and the best tool available: EXPLAIN EXTENDED You cannot do any better than have the DB tell you exactly what it is going to do with your query. Then you can experiment with changing / adding / removing clauses and see how it would affect the query plan produced by EXPLAIN. For example if EXPLAIN says the query would generate a temp table you could often achieve improvement by managing the same temp tables explicitly. Many times you can get a huge performance lift by using a "group by index". You could identify and rewrite un-indexed table scans too. I've found lots of other "unobvious" optimizations that cut down queries that ran for days or hours to minutes or seconds. Here are a few references to get started- 1) http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html http://dev.mysql.com/doc/refman/5.5/en/execution-plan-inform... 2) http://www.mysqlperformanceblog.com/2006/07/24/extended-explain/ http://www.mysqlperformanceblog.com/2006/07/24/extended-expl... 3) http://www.mysqlperformanceblog.com/2010/06/15/explain-extended-can-tell-you-all-kinds-of-interesting-things/ http://www.mysqlperformanceblog.com/2010/06/15/explain-exten...

offbyone 15y ago

I've always believed those tools must exist, but sorting out the SEO crap and advertising copy that gloms up search results for them is painful. Can you name any Linux/Mac tools like that?

DanielBMarkham 15y ago
Not linux, but I have a windows example. Being an El Cheapo, what I do from a windows box is fire up MS Access, link into the database (and this works even if the database is on a *nix box somewhere), then drag the text of the query into the graphical query builder tool. It's taken some pretty complex stuff apart for me in the past. It's been a while since I last deep-dove in a complex database, so I don't have any other examples handy. Sorry. Maybe somebody else can pick up the thread. I remember reviewing a bunch of them several years ago -- these tools have been around for a long time.
myth_drannon 15y ago
on PostgreSQL it's part of pgAdmin you don't need a separate tool
shrike 15y ago
I haven't found anything for Mac/Linux to do it but on Windows Visio can actually do a good job of visualizing a schema and SP structure.

waqf 15y ago

I want to see someone use a language model for SQL to reconstruct the query just from that photograph, and then debug it.

dmk23 15y ago

I use the cheapest and the best tool available: EXPLAIN EXTENDED You cannot do any better than have the DB tell you exactly what it is going to do with your query. Then you can experiment with changing / adding / removing clauses and see how it would affect the query plan produced by EXPLAIN. For example if EXPLAIN says the query would generate a temp table you could often achieve improvement by managing the same temp tables explicitly. Many times you can get a huge performance lift by using a "group by index". You could identify and rewrite un-indexed table scans too. I've found lots of other "unobvious" optimizations that cut down queries that ran for days or hours to minutes or seconds. Here are a few references to get started- 1) http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html http://dev.mysql.com/doc/refman/5.5/en/execution-plan-inform... 2) http://www.mysqlperformanceblog.com/2006/07/24/extended-explain/ http://www.mysqlperformanceblog.com/2006/07/24/extended-expl... 3) http://www.mysqlperformanceblog.com/2010/06/15/explain-extended-can-tell-you-all-kinds-of-interesting-things/ http://www.mysqlperformanceblog.com/2010/06/15/explain-exten...