CODEWITHSUNDEEP
ruby-on-railsdatabaseactiverecord
slindsey3000
slindsey3000

Reputation: 4269

Create an index for this query Heroku says is my slowest

Heroku says this is my slowest query. How would your migration look to create an index to speed this up?

It is ordered by created_at: :asc ... not sure how this will work into the index...

Here is the query from Heroku dashboard:

SELECT "line_movements"."game_moneyline_home"
FROM "line_movements"
WHERE "line_movements"."event_id" = $1
ORDER BY  "line_movements"."created_at" ASC

Speed this up with an index?

Upvotes: 0

Views: 31

Answers (1)

dileep nandanam
dileep nandanam

Reputation: 2895

You can index event_id offcource. Then if you really want to speed up the query, use integer timestamp for created_at. You can do something like

#add column created_at_int:integer to line_movements
#line_movement.rb
after_create :store_unix_time
def store_unix_time
  update_attributes created_at_int: created_at.to_i
end
#and get the time converted back by
def created_at_timestamp
  DateTime.parse(Time.at(created_at_int).to_s).in_time_zone
end

sql sort by integer is faster than sort by datetime.

Upvotes: 1

Related Questions