how does a SQL query work?

How does a SQL query work? How does it get compiled? Is the from clause compiled first to see if the table exists? How does it actually retrieve data from the database? How and in what format are the tables stored in a database?

I am using phpmyadmin, is there any way I can peek into the files where data is stored? I am using MySQL

Answer

Well…

• First you have a syntax check, followed by the generation of an expression tree – at this stage you can also test whether elements exist and “line up” (i.e. fields do exist WITHIN the table). This is the first step – any error here any you just tell the submitter to get real.
• Then you have…. analysis. A SQL query is different from a program in that it does not say HOW to do something, just WHAT THE RESULT IS. Set based logic. So you get a query analyzer in (depending on product bad to good – oracle long time has crappy ones, DB2 the most sensitive ones even measuring disc speed) to decide how best to approach this result. This is a really complicated beast – it may try dozens or hundreds of approaches to find one he believes to be fastest (cost based, basically some statistics).
• Then that gets executed.

The query analyzer, by the way, is where you see huge differences. Not sure about MySQL – SQL Server (Microsoft) shines in that it does not have the best one (but one of the good ones), but that it really has nice visual tools to SHOW the query plan, compare the estimates the the analyzer to the real needs (if they differ too much table statistics may be off so the analyzer THINKS a large table is small). They present that nicely visually.

DB2 had a great optimizer for some time, measuring – i already said – disc speed to put it into it’s estimates. Oracle went “left to right” (no real analysis) for a long time, and took user provided query hints (crap approach). I think MySQL was VERY primitive too in the start – not sure where it is now.

Table format in database etc. – that is really something you should not care for. This is documented (clearly, especially for an open source database), but why should you care? I have done SQL work for nearly 15 years or so and never had that need. And that includes doing quite high end work in some areas. Unless you try building a database file repair tool…. it makes no sense to bother.