Show HN: I wrote a free eBook about many lesser-known/secret database tricks

Since every message in the constant social media stream vanishes after a few days, I had to do something about it. Knowledge must be preserved. I sat down for a few weeks and reworked every example and every text to create an ebook that you can read in an evening and still impart tons of knowledge.

And so "Next-Level Database Techniques for Developers" was born.

I've watched that last part unfold on many occasions. Usually I'd be brought in because the developers have done everything down to "copying the data to denormalized tables[0]" to try to sort out slowness. Most of the time, just "blindly investigating the schema" will turn up something frightening that the ORM did, or a mess of inappropriate indexes. Two indexes on a table used by nearly every query in the database caused 30 second requests to yield sub-second results in one case[1]. I'm never willing to walk in and promise that, but I can't think of a time it hasn't happened when similar circumstances were presented to me.

So if you're dragging your feet about learning SQL, take this as my encouragement: it's one of those things where the rewards come quick and the effort is far less than you probably expect.

[0] ... with a broken sync process that has to be run carefully b/c it hammers the already over-sized database every time it fires.

[1] If memory serves, it was an account table ... used a GUID between the app and the database, but primary key was an integer auto-incrementing field which was used as the FK to other tables. All I remember was adding a unique index to the GUID field and including the e-mail address/name columns which were included in every query. Ran the fix in production and it felt like "the dam broke".

Archived copy: https://web.archive.org/web/20221202231259im_/https://sqlfor...

Having no email-wall would probably result in fewer registrations. But I do wonder if you had all the pages available -- meaning they would be indexed by Google and show up in search results -- with a subscribe to my newsletter button, if that would have a comparable number of registrations because you would have more people seeing the button? And they might be potentially higher quality registrations, too, if you want a community.

If your end goal is to share your knowledge then making the entire book public is probably the better option.

In short I'm just confused what the goal of the email-wall is!

(Also, one of my favorite techniques along the lines of your Lock Contention example: https://www.enterprisedb.com/blog/what-skip-locked-postgresq... is an amazing way to have a foolproof work queue without introducing things like Redis!)

Automatic no.

https://github.com/midnightmonster/activerecord-summarize

Thanks for writing it!

I also can't C&P from your pages as they're images, but Ghost Conditions Against Unindexed Columns has '... AND type = in (3, 6, 11)' - is that right or did you mean 'type in (...)'. It also talks about multi-col indexes as being more useful in some cases, true, but very elementary.

This is good and well done, but please don't oversell it.

If you ask me to give you my PII then it's not free, is it?

Are you not aware how many "free ebook" spams are being sent around, usually from disreputable SEO people begging you to link them from your site? The fact that you registered your own domain is another deep red flag. Clearly there are ulterior motives here aside from "I want to help the world by putting our a free ebook".

1) movie_actors (movie_id, actor_id, order_index)

2) movie_watchlist (movie_id, user_id, created_at)

3) movie_ratings (movie_id, user_id, rating, created_at)

I know strange question, but most guides suggest all these table should have autoincrement id column

LE. Almost nothing is applicable for MS SQL; some stuff is built into T-SQL, so there is no need for tricks, others have completely different solutions.

[1]: https://www.brentozar.com/blitz/

For example the first recipe will not work as expected with out a multi-column unique index (specifically the MySql example).

I feel that the preexisting knowledge required for such examples does not align with the stated target audience for the book.

Only critique is that it would be super useful to include some example query results for each section so show the effect.

I'm fascinated by DBMS each day I use them.

The offering is called an eBook but I'm not seeing any way to download a local copy as PDF or otherwise.

https://blog.jooq.org/sql/

https://winand.at/sql-slides-for-developers

While it does look really handy and well put together, unfortunately none of the tips in the book that I read before giving up are useful to someone using mssql

    -- MySQL
    SELECT * FROM example WHERE NOT(column <> 'value');
    -- PostgreSQL
    SELECT * FROM example WHERE column IS DISTINCT FROM 'value';

NOT(column <> 'value') seems to be wrong

Did you mean

NOT(column <=> 'value')

?

Red button: Give me $5

Blue button: Give me your email.

Everyone's happy! Sharing the results would be an icing on the cake.