Ups, if this is you take away, then I've done something wrong.
Let me correct that: The order of columns in an index matters, not the order of conditions in the where clause.
Not only you did a comment on previous post, but it's a comment from the author of an awesome guide [https://use-the-index-luke.com].
Markus, I can assure you, that your guide has saved a lot of machine-computed-hours over the world with less energy consumption/wasted. Great work. :)
If you have an index on <companyId, userId>, and you query with a userID only, that index wont be used. But if the index was <userId, companyId> then that index would be used. Or if you supplied both userId and companyId in your query, then either index would work.
where id in (select id from company)
Surprisingly, it could be used in some circumstances, just not for the regular seek.
If the index is small (compared to the base table), the DBMS may decide to perform a full index scan (instead of the full table scan), especially if your SELECT list doesn't contain columns which are not in the index.
And Oracle can employ so called "skip scan" if it realizes that the number of distinct companies is small. This is essentially a separate seek under each distinct company.
It has been discussed previously in HN .
they're a must have resource for advanced SQL.
A website that looks like a SaaS.
Hugely overpriced paperback version.
No book summary available.
A free chapter banner that doesn't link anywhere.
No possibility to buy on different stores.
While it's still a very decent book on SQL syntaxes and advanced queries (I bet even 10y+ PostgreSQL users don't know half of what PostgreSQL can do) here is why I don't recommend it:
- poorly written instructions on how to set up Postgres 11 with all those crazy extensions
- No epub version (it's simply broken and nobody responds on my emails, instead I get a reminder to continue my learning course which has 2 links, a short intro and a link to the book. I've stopped at 50% just because the book was downloaded from another browser and now I get these irritating emails)
- There is a lot of Python in the book, I want to learn SQL and PostgreSQL, not Python or any other sort of languages. I use Py professionally but it does not mean I want it to be tied to SQL.
- There are unrelated interviews, for instance, a guy who worked on Django ORMs (how is this related to my SQL knowledge)
Don't get me wrong, don't treat this comment as a rant about how bad the book is, the SQL tricks are really awesome there, however if you think about buying this book, go for 49$ version and expect to have some pain to setup everything.
I am like 50/50 on asking for refund
And yes, it's that Ullman.
I think the number of people needing truly advanced books is small whereas a lot of people need to jump from beginners to intermediate, or intermediate to advanced.
The book is a mess, but its structure illustrates its several points.
I wonder what are alternatives (video lectures or textual resources) with more hands-on approach implementing database systems.
Otherwise there is surprisingly a lack of information about architecting databases online and you have to go to the source code of SQLite and PostgreSQL. I suspect this is because databases are some of the most complex systems in computer science and involve many trade secrets.
Inspired by this post, I started yet another:
I also created a bunch of animations to help people build mental models for joins, aggregations, window functions, and subqueries which I find is the main blocker for people using SQL optimally: https://dataschool.com/how-to-teach-people-sql/
Sometimes it makes sense to roll up the sleeves and do unspeakable things with SQL, but that isn't some sort of win.
I can think of two other options, which I personally both dislike:
1. Some fancy graphical ETL monster which takes ages to learn and where the learned skills are more or less untransferable to anywhere else. And which, at the end, makes same things as raw SQL but just in a more opaque way.
2. Build the complexity outside database. Yes, you avoid complex SQL, but you also lose quite a lot. SQL prohibits you from doing quite a many different stupid mistakes with your data. At least 99 times out of 100 databases have better performance making the complicated calculations than your home brewed solution outside the database (Yes, I agree. The exceptions can be notable...) Finally, reusability of the results is way easier if you keep as much calculations as possible in the database.
But I can't say that I love hairy SQL, so if there are better ways, I would be keen to have a look.
I totally agree the options are all somewhat unpleasant. I've found dbt to be a nice step in the right direction.
(this is a plug from a happy customer!)
It's a pity that Ingres's QUEL has disappeared; the competition would be good for SQL.
Id agree with you about keeping it as simple as possible
That being said, people do dumb stuff with SQL all the time;
The worst query I have ever seen was 11MB, megabytes.
The worst query plan I have ever seen generated a plan that was ~130MB.
I regularly have people ask questions in #sqlhelp (https://sqlps.io/slack/ SQL Server focused) like "Why can I only have 2100 parameters to a stored procedure?" (dude was super mad that I asked if his design might use some rework.)
There is no Advanced SQL; there's only "a very large amount of SQL", "exotic SQL", and "SQL no one should have written."
+ a stored procedure
+ the end-product of an ORM tool?