Tell me more ×
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.
up vote 4 down vote favorite
2

So...I have learned how to write select, update, delete queries as well as join queries. Even though my queries work most of times, I don't have a solid idea how my queries work internally. If I want to learn how my queries work internally, what materials should I study?

Do I need to study DBA certification? Would that help?

share|improve this question
When you say internally do you mean how the database software looks up the data or what the query is doing? – TheLQ Feb 26 '11 at 5:37
TheLQ // exactly. – Moon Feb 26 '11 at 6:03
1  
DBA certifications just teach you how to use the DB software, not it's internal workings. So no, they're not much help there. – System Down Feb 26 '11 at 7:33
I think most DBA certification will teach you internals to a certain level. Even if they dont ask about B+Trees, they will expect you to know an index seek is faster than a scan, and how to get there. – Justin Dearing Apr 1 '11 at 19:36
This question is quite broad and with the link in the accepted answer being inaccessible at the moment, it seems like this question needs to be closed in its current form. – Anna Lear♦ Dec 22 '11 at 19:10

closed as not a real question by Anna Lear♦ Dec 22 '11 at 19:05

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, see the FAQ.

5 Answers

up vote 4 down vote accepted

The link below may be useful,

http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf and WikiPedia.

This is a huge topic and models like RDBMS, FLATFILE etc. I hope this paper will be a good starting point, if not then I apologise.

share|improve this answer
+1, great paper. – dan_waterworth Feb 26 '11 at 7:07
up vote 2 down vote

read about relational database operators, and study the query plans generated for your queries

share|improve this answer
up vote 2 down vote

You can learn everything you want from wikipedia. You can start with how database records are stored against there primary key in a B+Tree. Then maybe go onto join algorithms and finally take a look at indices.

share|improve this answer
up vote 0 down vote

You might find this video on how SQL database engines work from the creator of SQLite useful.

share|improve this answer
up vote 0 down vote

I agree with all the responses with good information as to the theory of what happens. However, a good starting point from a practical standpoint is to look at the query plans of the queries you run. Sql server allows you to do this from SQL Server management Studio (SSMS), and most other databases have an EXPLAIN keyword.

This will show you what is going on with your queries, and what parts of the query are slowest.

Combining this level of knowledge (e.g. a index seek is faster than an index scan) with the low level theory (e.g. how a b-tree works) allows you to truly understand whats happening (A seek goes through the tree, and a scan reads the linked list of leaves)

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.