Optimizing rails sql query

Question

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.

Run the plain MySQL query in any tool (workbench etc.) with EXPLAIN and post the result into your question. Also post your current table structure SHOW CREATE TABLE table_name;. — FanoFN
– FanoFN, Commented Nov 3, 2022 at 0:22

spickermann · Accepted Answer · 2022-11-03 06:16:09Z

1

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?

edited Nov 3, 2022 at 6:16

answered Nov 3, 2022 at 6:10

spickermann

108k9 gold badges115 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimizing rails sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related