CODEWITHSUNDEEP
mysqlsqlruby-on-railsrubyactiverecord
rail_newbie
rail_newbie

Reputation: 21

Optimizing rails sql query

I'm a junior dev learning rails. I have this code that queries the db and it runs really slow.

query = Users
  .select("user_id")
  .where(user_id: [array_of_ids], article_id: "article_id")
  .where("date IN (?)", @date_range)

query = query.where(store_id: [store_ids]) if store_ids.any?

I have index on article_id, index on date, and index on user_id. But it didn't help and table is huge.

Can you help with what indices need to be added to speed up this query?

Tried adding individual indices and composite indices. It did not help with optimization. Probably I have incorrect indices.

Upvotes: 2

Views: 131

Answers (1)

spickermann
spickermann

Reputation: 107142

As far as I remember, it depends on the version of MySQL if it would use multiple different indexes on the same table on a query. Instead, I suggest having a combined, optimized index for this query.

In this example, I would add an index like this (order is important, the optional column needs to be at the end):

add_index :users, [:user_id, :article_id, :date, :store_id], name: 'index_for_user_id_lookup'

Note that you will give a custom name to this index because the auto-generated name of an index over four columns will certainly be too long.

And nitpicking: The query and be simplified to

query = Users
  .select("user_id")
  .where(user_id: [array_of_ids], article_id: "article_id", date: @date_range)
query = query.where(store_id: [store_ids]) if store_ids.any?

Upvotes: 1

Related Questions