0

I have a frequently used query where User model has_many :payments:

current_user.payments.where(status: "succeeded").order(id: :desc).page(params[:page]).per(params[:size])

Do I need an index on the primary key in :desc order?

add_index :payments, [:user_id, :status], order: {id: :desc})

Or can I omit the order?

add_index :payments, [:user_id, :status]

Edit:

The page() and per() in the query are from kaminari gem. Do I need additional indexes for these?

loop
  • 1,455
  • 3
  • 13
  • 25
  • include the order in the index for improved performance, but it's not required. The Kaminari methods `per` and `page` are not attributes on the model, so they aren't indexable. And anyway `per` and `page` are just syntactic sugar for limit and offset, so indexing has no meaning. – Les Nightingill Aug 01 '21 at 17:00
  • @LesNightingill Does that mean `add_index :payments, [:user_id, :status]` would also speed up the query with ordering? Just not as much as the index with `order`? – loop Aug 01 '21 at 18:41
  • probably! I'm not sure... if you're using mySQL, the ref is here: https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html. But my suggestion would be to write a small test with a v. large number of models in the db, and time it. Or try looking at the `.explain` output for the query to see if the index is used (if it's used, performance will be better) – Les Nightingill Aug 01 '21 at 18:53

1 Answers1

0

A few pointers and maybe an answer to your question:

I see you're querying on .where(status: "succeeded"), is a this an Enum? That would also make more efficient queries.

Also, why order it by id? It seems more logical to me to order payments by created_at.

Then I wonder if you have defined the relation between users and payments with an add_reference. This would create foreign key relations, which are also faster.

Then finally, adding an extra index is always possible, but it really depends on multiple things if it's going to make a difference:

PRO:

  • Finding a record will become quicker when the data search becomes big (Not sure, something like >100000 rows)

CON:

  • Inserts and updates will become slightly slower (Every insert requires an extra index table to be updated)

Personally I would say that the default index from the add_reference between users and payments will suffice for a very, very long time.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Gijs P
  • 1,325
  • 12
  • 28
  • Thanks, I have previously ordered by `created_at`, but I was thinking if I order by `primary key`, i could possibly skip the need to index. Are you suggesting to `add_reference` instead of adding the index myself? If I did not go with the `add_reference` index, which of the indexes that I mentioned in my post would be best for my query? – loop Aug 01 '21 at 18:40
  • @Gijs why would an enum be faster? Are you saying that comparing integers is faster in sql than comparing strings? Do you have a reference for that? – Les Nightingill Aug 01 '21 at 18:57
  • @loop an `add_reference` is always preferred because it links the two tables on a database level, reducing the chance of malformed data (like a payment without a valid user). @LesNightingill yes, an (Tiny)Int only takes less space, so is faster to compare and retrieve: https://stackoverflow.com/questions/2346920/sql-select-speed-int-vs-varchar – Gijs P Aug 01 '21 at 19:59