3 ms·

Knowing what indexes you need and why is far from being some kind of esoteric specialized knowledge of database internals. That and some basic relational modeli

by gfody 11y ago

Knowing what indexes you need and why is far from being some kind of esoteric specialized knowledge of database internals. That and some basic relational modeling techniques are the very minimum prerequisites to being able to leverage an RDBMS competently. Learning these things is not as difficult as trying to use an RDBMS without learning them. I can't say I get the comparison to machine code. SQL is probably the highest level language there is.

tmptmp 11y ago
I agree. >>Learning these things is not as difficult as trying to use an RDBMS without learning them. Very aptly put. But I guess mastering SQL is a rather difficult task as SQL is a very high level language (may be even higher than Haskell minus the great type system of Haskell). It may be because of such a high level SQL seems difficult for many people. No wonder, the ORM crap sells so much, they sell almost impossible dreams (read snake oil) to naive people who don't understand much about SQL, data modeling, database system and OS. Also the relational modeling requires a lot of deep thinking to get yourself an effective schema. The magic art of getting indexes right to a large extent, relies on your understanding of the particular domain and the basics of relational modeling, the database internals don't come in picture too much. Of course, getting enough performance gains and squeezing time/space here and there requires some knowledge of database internals but when you reach to those levels of optimizations, NoSQL based solutions may as well require you to know enough details of their implementations. Take home lesson I learnt: these things (very high performance, efficiency, scaling) don't come for free.
- tome 11y ago
  > SQL is a very high level language (may be even higher than Haskell minus the great type system of Haskell). I have a solution for that :) https://github.com/tomjaguarpaw/haskell-opaleye/ https://github.com/tomjaguarpaw/haskell-opaleye/
  - tmptmp 10y ago
    Looks great, will try it out.
walshemj 11y ago
Yep you can learn the basics of decent SQL in a week if you cant even hack that well get a job at starbucks - working in tech is not for you.
Retra 10y ago
The database should already know what indexes you need just by looking at how you access the data. Explicitly choosing indexes is explicitly making a choice off time-space tradeoffs, and you have to do it at a very low level: which columns do you build indexes on? How many do you build? It really isn't that far off from choosing the layout of a struct in C and having to know how it's packed and aligned. >Knowing what indexes you need and why is far from being some kind of esoteric specialized knowledge of database internals. I'll concede that, but the issue still exists. Does your database have DROP TABLE IF EXISTS or not? Some do, some don't. If it doesn't, how do you achieve the same functionality? A given database offers thousands of operations, and the syntax, performance, and semantics of each varies by quite a bit. My point is, you have to learn the specifics of the database you're using to do general purpose data storage stuff.

by gfody 11y ago

tmptmp 11y ago

I agree. >>Learning these things is not as difficult as trying to use an RDBMS without learning them. Very aptly put. But I guess mastering SQL is a rather difficult task as SQL is a very high level language (may be even higher than Haskell minus the great type system of Haskell). It may be because of such a high level SQL seems difficult for many people. No wonder, the ORM crap sells so much, they sell almost impossible dreams (read snake oil) to naive people who don't understand much about SQL, data modeling, database system and OS. Also the relational modeling requires a lot of deep thinking to get yourself an effective schema. The magic art of getting indexes right to a large extent, relies on your understanding of the particular domain and the basics of relational modeling, the database internals don't come in picture too much. Of course, getting enough performance gains and squeezing time/space here and there requires some knowledge of database internals but when you reach to those levels of optimizations, NoSQL based solutions may as well require you to know enough details of their implementations. Take home lesson I learnt: these things (very high performance, efficiency, scaling) don't come for free.

tome 11y ago
> SQL is a very high level language (may be even higher than Haskell minus the great type system of Haskell). I have a solution for that :) https://github.com/tomjaguarpaw/haskell-opaleye/ https://github.com/tomjaguarpaw/haskell-opaleye/
- tmptmp 10y ago
  Looks great, will try it out.

walshemj 11y ago

Yep you can learn the basics of decent SQL in a week if you cant even hack that well get a job at starbucks - working in tech is not for you.

Retra 10y ago

The database should already know what indexes you need just by looking at how you access the data. Explicitly choosing indexes is explicitly making a choice off time-space tradeoffs, and you have to do it at a very low level: which columns do you build indexes on? How many do you build? It really isn't that far off from choosing the layout of a struct in C and having to know how it's packed and aligned. >Knowing what indexes you need and why is far from being some kind of esoteric specialized knowledge of database internals. I'll concede that, but the issue still exists. Does your database have DROP TABLE IF EXISTS or not? Some do, some don't. If it doesn't, how do you achieve the same functionality? A given database offers thousands of operations, and the syntax, performance, and semantics of each varies by quite a bit. My point is, you have to learn the specifics of the database you're using to do general purpose data storage stuff.