3 ms·

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

• Vvector 3y ago
  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
  • lazide 3y ago
    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.
  • abtinf 3y ago
    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.