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!

  • 7
    The answer to this is more at the level of a course at the university than the simple question/answer preferred at StackOverflow. 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. 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
  • I was asking myself the same question. Here is the most useful resources list I found : github.com/rxin/db-readings especially this one (Anatomy Of A Database System) : mitpress.mit.edu/sites/default/files/titles/content/…
    – nha
    Aug 29 '14 at 15:31

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.


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/

  • Good answer, but bear in mind that SQLite is essentially a single user database and its implementation varies greatly from "bigger" database engines. Aug 11 '11 at 6:29
  • 3
    @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 Aug 11 '11 at 6:36