Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I was wondering about how databases like MySQL and SQLite are developed to run as quickly and as well as they do.

I'm looking for answers to questions like:

  • How is SQL token-ized? Do you build an AST and compile/interpret like any other language?
  • How should one handle connections to the database? TCP? IPC? How do big-name databases do it?
  • How is the data is these fast relational databases stored? I know SQLite uses a flat file -- seems like a synchronization nightmare.

Obviously, I don't expect anyone to just explain all of relational database theory to me -- but I've been looking around and I'm really at a loss for references to start learning about this stuff.

To answer the inevitable "why don't you just use MySQL?": (a) because I'm curious and (b) maybe someday I'll want to design my own database with a specific use in mind.

EDIT: I guess I should've made this more clear. I realize this is a broad topic. I am not looking for someone to say "oh, you design a database by..." If this is a topic covered in a large number of books, what are those books? If this is covered well in a P.h.D. thesis you know of, what is that thesis? I am very aware that this is a broad topic -- I'm not asking for a specific answer for a broad question, I'm asking for specific resources for a broad question. As always, thanks for all the help!

share|improve this question

migrated from stackoverflow.com Aug 11 '11 at 6:16

This question came from our site for professional and enthusiast programmers.

closed as not a real question by Robert Harvey, tcrosley, ChrisF Aug 11 '11 at 10:19

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

7  
The answer to this is more at the level of a course at the university than the simple question/answer preferred at StackOverflow. –  Albin Sunnanbo Aug 11 '11 at 4:51
2  
From the site FAQ: "Your questions should be reasonably scoped. If you can imagine an entire book that answers your question, you’re asking too much." ~~ To be clear, the scope of your question is more than even a book can cover. If you just want to gain some high level understanding, start with b-trees in wikipedia and follow links until you're satiated. –  Dan Grossman Aug 11 '11 at 5:51
    
This question is well beyond the scope of stack overflow. A modern DBMS is incredibly complex. I would suggest if you really want to know what's going on under the hood of a RDBMS, download the source of an open source DB. dev.mysql.com/downloads/mysql postgresql.org/ftp/source –  magicmike Aug 11 '11 at 5:57

1 Answer 1

Download a copy of SQLite and start going through the code. That should give you a very decent grounding in the fundamental concepts of DBMS design.

The SQLite author has extensive notes on his website about how SQLite was designed, and the database engine is small enough that you should be able to wrap your mind around it.

http://www.sqlite.org/

SQLite is written in C. If you prefer a more "modern" language to read the code, try the C# version of the SQLite engine here: http://code.google.com/p/csharp-sqlite/

share|improve this answer
    
Good answer, but bear in mind that SQLite is essentially a single user database and its implementation varies greatly from "bigger" database engines. –  James Anderson Aug 11 '11 at 6:29
2  
@James: SQLite is a multi-user database capable of running small websites. The only caveat is that writes briefly lock the entire database, but overall it's probably more robust than an Access database. See sqlite.org/whentouse.html –  Robert Harvey Aug 11 '11 at 6:36