2 ms·

It is kind of what you might expect for many of us. Tough problems pushed me to learn more. I got started before the time of ORMs being popular. It forced me to

It is kind of what you might expect for many of us. Tough problems pushed me to learn more. I got started before the time of ORMs being popular. It forced me to learn a lot of fundamentals. Find data sets and interesting things to do with them. Teach yourself about the database internals. RDBMS is rows and columns. Queries are planned. Planner tries to use most optimized query. Example, you join on a column that isn't indexed. To find every matching row DB has to scan entire table. Hello O(n^2). The details a query planner gives can be overwhelming. Learn every bit of it. Find ways to query data efficiently you would just deal with it app code. Learn subqueries. Learn aggregation. Learn aggregation with subqueries. But here is the real, fundamental truth about RDBMS and SQL. It is essentially a functional data environment, like a spreadsheet. Once you adapt to the functional mindset of SQL it can feel effortless to do things you might not have even thought possible with SQL.

twic 8y ago
> Example, you join on a column that isn't indexed. To find every matching row DB has to scan entire table. Hello O(n^2). It's only O(n^2) if your DBMS uses nested loops to do this join. If it uses a merge join it's O(n log n) (plus you get an order by for 'free'), and if it uses a hash join it's O(n). MySQL, last I checked, only supported various forms of nested loops, but first-class databases like PostgreSQL support the others.
- bitexploder 8y ago
  Good point, and a good reason to learn how your DBMS of choice does common operations. Most query planners will explicitly tell you what yours is doing. I remember even back a long time ago MSSQL 7 had a nice GUI for its "query explain" and pgAdmin also gives a really nice graphical view of query plans, which really helps visualize when it is looping, doing hash lookups, etc. I suppose that would be part of the recommendation for learning: use a graphical query plan view if you can find a client that supports it :) http://www.postgresonline.com/journal/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html http://www.postgresonline.com/journal/archives/27-Reading-Pg... (example pictures if anyone reading this hasn't seen it before), it is pretty cool.

twic 8y ago

> Example, you join on a column that isn't indexed. To find every matching row DB has to scan entire table. Hello O(n^2). It's only O(n^2) if your DBMS uses nested loops to do this join. If it uses a merge join it's O(n log n) (plus you get an order by for 'free'), and if it uses a hash join it's O(n). MySQL, last I checked, only supported various forms of nested loops, but first-class databases like PostgreSQL support the others.

bitexploder 8y ago
Good point, and a good reason to learn how your DBMS of choice does common operations. Most query planners will explicitly tell you what yours is doing. I remember even back a long time ago MSSQL 7 had a nice GUI for its "query explain" and pgAdmin also gives a really nice graphical view of query plans, which really helps visualize when it is looping, doing hash lookups, etc. I suppose that would be part of the recommendation for learning: use a graphical query plan view if you can find a client that supports it :) http://www.postgresonline.com/journal/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html http://www.postgresonline.com/journal/archives/27-Reading-Pg... (example pictures if anyone reading this hasn't seen it before), it is pretty cool.