As another example I've found of where the theory is helpful: I have often seen two dimensional data put into a relational database, where the person wants to query over a range of values on both dimensions. (I.e., they want to find everything inside of a rectangle, were you to graph the points.) Broadly, I've found these fall into two categories:
1. Lat/lng positions (find all rows where lat between [lat1, lat2] and lng between [lng1, lng2]
2. start time/end time pairs (find all rows where the event is in or overlaps this range)
Then those two dimensions/attributes are put into what is often a B+ Tree or similar index. B+ Tree indexes can really only efficiently query on one range, and only if every preceding attribute in the tuple that the index is built from is specified exactly. The first example w/ lat lngs is particularly bad.
But a lot of developers working w/ databases have no idea what a B+ Tree, or even any tree really means. They get that it's a tree, and that it's ordered. But there's a gap between that and somehow being able to think it through and arrive at the fact that the database must either:
1. walk a lot of rows that aren't going to match (they typically do this)
2. or do a hell of a lot of range scans (they typically do not do this, and it's not really always possible)
Often I find that they think it is </>/= with each attribute individually, when it is really more like the tuple being indexed as a whole has a < operator implemented for it (by the database). Thankfully the analogy of find all people whose first names start with "Th" in a index ordered first by last name usually clears it up somewhat fast, but it took me a while to find that analogy.
(My CS degree included a course on relational databases. We covered everything from theory, such as relational algebra — operations on sets of tuples — to concrete implementations such as PostgreSQL and how to efficiently store indexes/data on disk. The professor I had was absolutely amazing, though I did not really realize this at the time.)
1. Lat/lng positions (find all rows where lat between [lat1, lat2] and lng between [lng1, lng2]
2. start time/end time pairs (find all rows where the event is in or overlaps this range)
Then those two dimensions/attributes are put into what is often a B+ Tree or similar index. B+ Tree indexes can really only efficiently query on one range, and only if every preceding attribute in the tuple that the index is built from is specified exactly. The first example w/ lat lngs is particularly bad.
But a lot of developers working w/ databases have no idea what a B+ Tree, or even any tree really means. They get that it's a tree, and that it's ordered. But there's a gap between that and somehow being able to think it through and arrive at the fact that the database must either:
1. walk a lot of rows that aren't going to match (they typically do this)
2. or do a hell of a lot of range scans (they typically do not do this, and it's not really always possible)
Often I find that they think it is </>/= with each attribute individually, when it is really more like the tuple being indexed as a whole has a < operator implemented for it (by the database). Thankfully the analogy of find all people whose first names start with "Th" in a index ordered first by last name usually clears it up somewhat fast, but it took me a while to find that analogy.
(My CS degree included a course on relational databases. We covered everything from theory, such as relational algebra — operations on sets of tuples — to concrete implementations such as PostgreSQL and how to efficiently store indexes/data on disk. The professor I had was absolutely amazing, though I did not really realize this at the time.)