3 ms·

I liked the article, and I know this is nitpicking, but I disagree that creating an index is the best solution for eliminating the sequential scan. Hopefully th

by tofflos 12y ago

I liked the article, and I know this is nitpicking, but I disagree that creating an index is the best solution for eliminating the sequential scan. Hopefully the next article mentions it. Just drop sort clause. Returning the user with the lowest id seems like an unusual use case. Imagine searching for a name in a phone book and only finding the oldest person with that name, or the person with the lowest phone number. More common cases are checking whether a user exists, which doesn't require a sort, or finding all the users with a particular name, which I guess could be paged using limit and offset.

pat_shaughnessy 12y ago
Oh absolutely. I thought about saying that, and just ran out of energy before posting the article this morning. The reason I used this particular SQL statement as an example is that ActiveRecord (a Ruby ORM) generates it from a fairly simple, common Ruby expression. I suppose ActiveRecord could be improved to drop the sort when you know there's one one possible match.
- talaketu 12y ago
  Unless "name" is unique, there could be multiple matches. Asking for the "first" demands an ordering. Perhaps (a) the user doesn't care what ordering is used, and/or (b) (such as in your example in the preceding post) hasn't specified an order (and apparently PK ordering is defaulted to PK - a model option?). It would be error-prone for ORM to take (b) as implying (a).
  - pat_shaughnessy 12y ago
    Yes, good point. Rails has a lot of conventions like this - that "first" implies ordering by primary key (by default). I just imagine that most Rails developers don't think about sorting and its implications when they ask for the first record.
dbenhur 12y ago
Even if you don't care about the id ordering, unless "Captain Nemo" occurs so frequently as to be likely found in the first few pages of the table, the index is a big help.
empthought 12y ago
Ordering by ID can be used in keyset pagination as well as limit/offset: http://use-the-index-luke.com/no-offset http://use-the-index-luke.com/no-offset

by tofflos 12y ago

pat_shaughnessy 12y ago

Oh absolutely. I thought about saying that, and just ran out of energy before posting the article this morning. The reason I used this particular SQL statement as an example is that ActiveRecord (a Ruby ORM) generates it from a fairly simple, common Ruby expression. I suppose ActiveRecord could be improved to drop the sort when you know there's one one possible match.

talaketu 12y ago
Unless "name" is unique, there could be multiple matches. Asking for the "first" demands an ordering. Perhaps (a) the user doesn't care what ordering is used, and/or (b) (such as in your example in the preceding post) hasn't specified an order (and apparently PK ordering is defaulted to PK - a model option?). It would be error-prone for ORM to take (b) as implying (a).
- pat_shaughnessy 12y ago
  Yes, good point. Rails has a lot of conventions like this - that "first" implies ordering by primary key (by default). I just imagine that most Rails developers don't think about sorting and its implications when they ask for the first record.

dbenhur 12y ago

Even if you don't care about the id ordering, unless "Captain Nemo" occurs so frequently as to be likely found in the first few pages of the table, the index is a big help.

empthought 12y ago

Ordering by ID can be used in keyset pagination as well as limit/offset: http://use-the-index-luke.com/no-offset http://use-the-index-luke.com/no-offset