Hacker News Reader

Comment by eterm | Hacker News Reader

That goes against my intuition, because the performance would be more impacted (beneficial to have an index) where you have very few bits set on a boolean field.

If you have 10m users and 100 have "IsPremium = 1" then an index massively speeds up anything with WHERE IsPremium = 1, compared to if the data is fairly evenly spread between IsPremium = 1 and IsPremium = 0 where an index won't be much benefit.

So low sales would increase the need for an index.

That said, I'm assuming searching for "Users WHERE IsPremium = 1" is a fairly rare thing, so an index might not make much difference either way.

View on HN

We had lots of indexes on the user table; there was a separate table with details of paying users.

You wouldn't need any of these WHERE clauses if you have all the premium users in one table. Even managing the users in both tables in pretty trivial when you just add someone as they pay and remove them as the premium account expires.

> paying-user table index-free

Presumably, that means they created a new table intended to be straight-joined back to the user table. No need to search a column.

Joining a table on a column without an index will require a linear scan of the column. You almost always want an index on JOIN columns

The primary key (userid?) will almost always be indexed implicitly. You’d usually have to go out of your way to avoid it.

So it was probably being joined by an indexed column, but without an explicitly defined index.

Two relations with a one-to-one relationship implies identical primary keys. Most implementations default to creating an index on the primary key.

In this case, the premium user table only needs the user id primary key/surrogate key because it only contains premium users. A query starting with this table is naturally constrained to only premium user rows. You can think of this sort of like a partial index.

One consequence of this approach is that queries filtering only for non-premium users will be more expensive.

Having a separate table containing all the premium users is different than an extra column in the normal user table. In the extra table example you don't really need an index (in premium user table) if you have only 100 premium users

Reply on news.ycombinator.com