trying to optimize sql query by adding foreign key to model

slime_saw 0
Viewed 41

I'm trying to optimize a sql query that is currently using a join and slowing things down. Right now, an account can have many users and users can have many sales. Instead of getting the account on a sale by using join with users, I want to store the account_id on the sale. I can see that the account_id is correctly showing up now on a sale, but it is nil.

Here is the migration:

  def change
    add_reference :bids, :account, type: :integer, after: :user_id, foreign_key: true
  end

user model:

  belongs_to :account
  has_many :sales

account model:

  has_many :users,  dependent: :destroy
  has_many :sales, through: :users

sale model:

  belongs_to :user
  has_one :account, through: :user

when a new sale is created, it hits the api and I first create the sale

   sale = Sale.new(company: item.company, sale_type: params[:sale_type])

after some logic of checking user, I place the sale with

   sale = item.place_sale!(user, params[:sale_type], params[:amount], params[:note], nil, 
   params[:custom_amount].present?)

at this point it's in the item model, where place_sale! is defined. I am likely misunderstanding, but I thought because of relations and after running the migration, the account_id would be set on the item. I did try grabbing it in item and bid model but was getting errors. any insight would be appreciated!

update: I was able to get this to work by altering my migration as such:

  def change
    add_reference :sales, :account, type: :integer, after: :user_id, foreign_key: {on_delete: :nullify}, index: true
    reversible do |dir|
      dir.up do
        ActiveRecord::Base.connection.execute("UPDATE sales JOIN users ON sales.user_id = users.id SET sales.account_id = users.account_id;")
      end
    end
  end

I also changed my sale and account model to no longer have relation to each other through users (since sale is now directly linked to account)

ruby ruby-on-rails mysql
1 Answers

For performance queries, I need to see the generated SQL and SHOW CREATE TABLE. For this one:

SELECT  `sales` . `store_id`
    FROM  `sales`
    INNER JOIN  `users`  ON `sales` . `user_id` = `users` . `id`
    WHERE  `users` . `account_id` = ?
      AND  `sales` . `status` = ?

These indexes may help with performance:

users:  INDEX(account_id, id)
sales:  INDEX(user_id, status)
Rick James0
Related