
Ask HN: How to get better in advanced SQL - dprophecyguy
How to get better at indexing?
How to get better at joins?
How to get better in SQL optimization?<p>Must to know things about SQL and maybe relational databases?
======
MarkusWinand
Since you ased about indexing in particular, here is my site about indexing:

[https://use-the-index-luke.com/](https://use-the-index-luke.com/)

You might also want to follow my other project:

[https://modern-sql.com/](https://modern-sql.com/)

~~~
alexpetralia
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!

~~~
frosted-flakes
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).

------
password4321
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](https://news.ycombinator.com/item?id=13417326)

------
hal_mcgee
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.

------
diehunde
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/](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.

------
antoineMoPa
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.

------
dkarp
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/](https://leetcode.com/problemset/database/)

------
Pamar
I would reccomend this book:

[https://www.amazon.com/Art-SQL-Stephane-
Faroult/dp/059600894...](https://www.amazon.com/Art-SQL-Stephane-
Faroult/dp/0596008945)

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

~~~
jppope
link?

~~~
deepaksurti
[http://shop.oreilly.com/product/9780128007617.do](http://shop.oreilly.com/product/9780128007617.do)

------
JeffRosenberg
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.

------
rayrrr
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.

------
amingilani
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

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

------
ksherlock
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.

