Ask HN: How to get better in advanced SQL | Hacker News Reader

Ask HN: How to get better in advanced SQL

132 pointsdprophecyguy21 comments

How to get better at indexing? How to get better at joins? How to get better in SQL optimization?

Must to know things about SQL and maybe relational databases?

View on HN

Since you ased about indexing in particular, here is my site about indexing:

https://use-the-index-luke.com/

You might also want to follow my other project:

https://modern-sql.com/

One more vote for use-the-index-Luke. Clear, concise and comprehensive - I used this quite a bit when studying for interviews or optimizing database performance. Great resource!

His book (SQL Performance Explained) is also very good. The book is not too long and not too dense. I'd buy it again (in fact, I probably will, because I left my copy with a former employer because they paid for it).

I was introduced to Markus' work by way of Wes McKinney's Twitter link [1].

This is deeper SQL-fu that many of us typically encounter. Highly recommend.

[1] https://twitter.com/wesmckinn/status/564526251591733248

I used your website to relearn SQL for work! I can't thank you enough for it and getting promoted.

I love use the index luke. Some of the best query advice I can get when I have to get something done faster.

Personally, the most important part of getting advanced with SQL is being able to vizualize the DB in your mind and how the query should look. Also, sometimes bad design is at fault than a query. If the design is too complicated or wrong a query is gonna be slow in relation to the amount of data it has to go over.

It's a bit off topic since it's not a resource for learning, but:

http://tatiyants.com/pev/#/plans

(Postgres Explain Visualizer)

When dealing with query optimization in practice, it helps a lot since it enables a clear, convenient and synthetic view of an EXPLAIN (ANALYZE).

Just yesterday, it helped me improve a big analytic query from executing in 10 minutes to 5 seconds by just adding a small index.

Ask HN: What is the best online resource to learn advanced SQL? (Jan 2017: 448 points, 97 comments)

https://news.ycombinator.com/item?id=13417326

Search for a copy of the DB2 SQL Cookbook. Lots of good explanations and examples. It's free and no longer updated as I guess it is just too darn useful...

One of the problems with advanced SQL is you get into how the vendors implement advanced features. In Oracle you will get familiar with ORA-xxx and coding around those. DB2 throws different error messages and so does SQL Server. I have had to rewrite SQL going from one implementation to another because of these differences.

Just by doing advanced exercises. For example take a look at the SQL homework for one of the CMU courses on databases:

https://15445.courses.cs.cmu.edu/fall2018/homework1/

While doing the exercises you'll be doing some research and looking at question in stack overflow.

What worked well for me was getting a job in a small industrial business with a lot of IOT sensors. This brought a lot of SQL queries to optimize...

Using tools like MySQL WorkBench or dBeaver is essential to try queries in a friendly interface (tools that show time spent on queries & better tables than command line). Ideally, you should also search for database normalization.

If you don't have a database to practice with, you can analyze a small business need (ex.: managing employee salary, address & tasks), then create the schema. Then, you should generate tons of fake data to have a substantial time difference when optimizing queries. Try selecting all employees and their tasks, with different sorts. Try finding all employees with an address that starts with 4. Find all employees with a "i" in their name and where the task name is "plumber", etc.

Also, read about rails/laravel migrations, as they are the best methods I know to manage database versions.

I've found the leetcode[1] database problems have helped me quite a lot - especially to see different ways of solving problems and how to do things in SQL that you didn't know you could.

1. https://leetcode.com/problemset/database/

I would reccomend this book:

https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/059600894...

"SQL for Smarties", Joe Celko -- an excellent resource

link?

http://shop.oreilly.com/product/9780128007617.do

Start generating and reviewing explain plans for your queries. There's a ton of information available about how the engine is implementing your queries, and learning how to think like the database engine will really help you level-up the way you think about SQL.

For SQL optimization, learn to use window functions and/or Common Table Expressions if available in your variant of SQL. For joins, others have mentioned EXPLAIN: you can use that command to see the "query plan" for any query, which is sort of the equivalent of assembly code. You should learn which of your SQL variant's "join algorithms" at the query plan level are the most performant, and aim to use those types of joins in your queries.

If you'd like to gamify the process, I recommend CodeWars[0]

They give you challenges that progressively get harder. You can choose from many different languages but SQL is one of them. I'm not affiliated with them, but I used them for a few months to get incredibly good with Ruby.

[0]: referral link: www.codewars.com/r/EbsMWQ

It depends a lot on the database flavor. My suggestion is to search for “tips and tricks“ for your particular DB.

Learn relational algebra. Learn relational calculus. You'll probably want to read a book or take a class on DB theory; blog posts won't cut it.

Reply on news.ycombinator.com