how does a SQL query work?

Question

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 1

This is a good question but cannot be completely answered here.

You can read the book Understanding MySQL Internals which will give you answers to most of your questions.

Answer 2

Well...

• First you ahve 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 does exist WITHIN the table). This is the first step - any error here any you jsut tell the submitter to get real.
• Then yo have.... analysis. A SQL query is different from a program in that it does not say HOW to do something, jsut WHAT THE RESULT IS. Set based logic. So you get a query analyzer in (depending on product bad to good - oracle long time hat crappy ones, db2 the most senstive 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, btw., is where you see hugh differences. Not sure about mySQL - SQL Server (Micsrosoft) 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 otoh had a great optimize rfor some time, measuring - i already said - disc speed to put it into it's estiamtes. 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 somthing you should not care for. This is documented (clearly, especially for an open source database), but why should you care? I do 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 datbase fil erepair tool.... it makes no sense to bother.

Answer 3

If your target product is SQL Server, then this article is a good place to start.