Hacker News Reader

Comment by franckpachot | Hacker News Reader

With an index on User (createdAt, id) and one on ContactMethod ( primary,ContactMethod,userId), it should be fast (check that the the execution plan starts with User). Except if lot of recent users have no phones, but that will not be better in a single table (except if columnar storage)

View on HN

I imagine so too. You’ll be able to interate over the users in order and hit an instant index on contact method.

    select
      *
    from
      user
    where
      exists (
        select
          true
        from
          contact_method cm
        where
          cm.contact_id = contact.id
          and cm.method = 'phone'
          and cm.primary
      )
    order by
      created_at desc
    limit 10

    —- partial index is even faster
    create index primary_phone on contact_method (contact_id) where method = 'phone' and primary;

See my reply here: https://news.ycombinator.com/item?id=37116015

Reply on news.ycombinator.com