Fetching items from database with "Outstanding" status I am fetching around more than 300000 items through a query:

@items = current_company.item_masters.user_hierarchies_filter(current_user).accessible_by(current_ability).paginate(:conditions =&gt; condition.compile, :include=&gt; includable_tables,
        :order =&gt; (sort_column + " " + sort_direction),
        :page=&gt; new_params[:page],:per_page =&gt; new_params[:per_page_records].blank? ? 10 : new_params[:per_page_records])


And the scope used in that query is : 

named_scope :user_hierarchies_filter, lambda { |user|
    {select: "DISTINCT item_masters.id", conditions: ["item_masters.company_id = ?", user.company_id]}
  }


It produces this log message: 

ItemMaster Load (43445.6ms)   SELECT DISTINCT item_masters.id FROM `item_masters` WHERE (item_masters.status IN ('Outstanding')) AND (((item_masters.company_id = 1) AND (`item_masters`.company_id = 1))) ORDER BY item_number asc LIMIT 331230, 10


It takes around 44 seconds to load items. Can anyone please suggest any option to reduce the loading time of this query?
As far as I can see this is a very old Rails code. Please follow these steps:


Refactor the code a little bit:

scope user_hierarchies_filter: lambda { |user|
  select('distinct item_masters.id').
  where("item_masters.company_id = ?", user.company_id)
}



and

    @items = current_company.item_masters.
    user_hierarchies_filter(current_user).accessible_by(current_ability).
    includes(includable_tables).
    order(sort_column + " " + sort_direction).
    paginate(page: new_params[:page],
             per_page: new_params[:per_page_records].blank? ? 10 : new_params[:per_page_records])



from the generated query:

SELECT DISTINCT item_masters.id 
FROM `item_masters` 
WHERE (item_masters.status IN ('Outstanding')) 
AND (((item_masters.company_id = 1) 
AND (`item_masters`.company_id = 1))) 
ORDER BY item_number asc LIMIT 331230, 10



I propose a couple of things:


Remove the duplicated condition on the company_id.
Explain the query and try to see why is so slow. I guess you are missing an index on the status, company_id and item_number columns: do things get better if you run the following migration?

add_index :item_masters, :company_id
add_index :item_masters, :status
add_index :item_masters, :item_number