ruby on rails - Efficient database query - avoiding reevaluation on page load -

i wondering if provide guidance in terms of best approach take in addressing problem facing rails application.

background have large database called product contains around 500,000 lines. create page (this called "details") enables users view information subsection of items in product.

initially want run active record query on product, yielding @matchingproducts - e.g. @matchingproducts = product.where(...).

there 2 slight complications:

1. @matchingproducts typically contains around 200 records, using paginate show these results 50 @ time
2. i able apply further filters @matchingproducts enable users fine tune results (e.g. @matchingproducts_filtered = @matchingproducts.where(...))

problem how should define variable @matchingproducts in such way query of entire database (product) performed once (i.e. on initial load of "details" page?

currently time consuming query of product rerun each time view next x records using paginate when @matchingproducts_filtered specified user

any guidance can provide on issue appreciated

as sergio tulentsev mentioned, should ensure have correct indexes setup. 500,000 records not tremendous amount modern, production-quality db on recent hardware, slowness in querying might 1 of first things at.

if loading lot of related associations/sub-associations, etc., n+1 query problem includes on query may with. if @ log , debugging activerecord queries, might see ton of queries single request , thought single query looking @ code. sign of n+1 query.

if don't need display association/subassociation/etc. data, maybe without it.

if doing heavy searching/filtering, might @ sunspot/solr. exploring burn more initial , recurring time need spending on problem, wouldn't suggest @ point. keep in mind later complex , varying queries larger dataset.

unfortunately, these types of questions relating increasing performance open-ended, many factors can involved, start.