# Pagination caching **URL:** \ **Category:** Rails\ **Created:** [January 12, 2008, 1:11pm UTC](https://www.ruby-forum.com/t/pagination-caching/125818 "2008-01-12T13:11:27Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![luma](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d6d6ee/32.png) [@luma](https://www.ruby-forum.com/u/luma)\ **Post date:** [January 12, 2008, 1:11pm UTC](https://www.ruby-forum.com/t/pagination-caching/125818/1 "2008-01-12T13:11:27Z")
Hi I have the following method: def search products = Product.find(:all, :conditions =\> [“category = ?”, params[:category]], :limit =\> 1000, :order\_by =\> “float\_rating DESC”) @shown\_items = my\_pagination(products, {:per\_page =\> 20, :page =\> params[:page]}) end There are 6 values for category. The order\_by term (which I really need) slows down the performance. So I’d like to cache the action. The problem is that it has two parameters, :category and :page, but I only know about caching with one parameter (which will be named :id). How can I do that without writing 6 different methods in the controller? Thanks Luma ---
**Author:** ![luma](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d6d6ee/32.png) [@luma](https://www.ruby-forum.com/u/luma)\ **Post date:** [January 12, 2008, 7:10pm UTC](https://www.ruby-forum.com/t/pagination-caching/125818/2 "2008-01-12T19:10:47Z")
On 12 Jan 2008, at 12:10, Luma wrote: > params[:page]}) > end > > There are 6 values for category. The order\_by term (which I really > need) slows down the performance. Don’t know about the caching, but perhaps the action itself can be made faster. What indexes do you have? If you have a multicolumn index on category, float\_rating then that should help. It’s also inherently a little wasteful to be fetching 1000 items but only displaying 20. Fred > So I’d like to cache the action. The problem is that it has two > parameters, :category and :page, but I only know about caching with > one parameter (which will be named :id). How can I do that without > writing 6 different methods in the controller? ---
**Author:** ![luma](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d6d6ee/32.png) [@luma](https://www.ruby-forum.com/u/luma)\ **Post date:** [January 13, 2008, 3:19pm UTC](https://www.ruby-forum.com/t/pagination-caching/125818/3 "2008-01-13T15:19:39Z")
There is an index on category and float\_rating. I’m trying to improve the action itself. My approach is counting the results first, and using :offset and :limit in the find-query. It’s only a bit faster. Is there a better way, or can I improve the code? def search paginate\_with\_offset(Product, 20, params[:page], “float\_rating DESC”, 1000, {:conditions =\> [“category = ?”, params[:category]]}) end def paginate\_with\_offset(search\_class, per\_page, page, order\_by, count\_limit, options = {}) @item\_count = [search\_class.count(:all, options), count\_limit].compact.min # count\_limit can be nil @page\_count = (@item\_count.to\_f / per\_page).ceil if @item\_count \> 0 @page = begin Integer(page) rescue 1 end @page = 1 unless (1…@page\_count).include?(@page) @offset = per\_page \* (@page - 1) @last = [@offset + per\_page, @item\_count].min limit = (@last-1) % per\_page +1 options = options.merge({:offset =\> @offset, :limit =\> limit}) options = options.merge({:order =\> order\_by}) if order\_by @items = search\_class.find(:all, options) else @items = [] end end Luma On 12 Jan., 19:00, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![luma](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d6d6ee/32.png) [@luma](https://www.ruby-forum.com/u/luma)\ **Post date:** [January 13, 2008, 6:13pm UTC](https://www.ruby-forum.com/t/pagination-caching/125818/4 "2008-01-13T18:13:21Z")
hi Fred, The index really includes (category, float\_rating) in that order. Indeed, I don’t really know how indexes work 😉 Martin On 13 Jan., 17:09, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![luma](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d6d6ee/32.png) [@luma](https://www.ruby-forum.com/u/luma)\ **Post date:** [January 13, 2008, 5:10pm UTC](https://www.ruby-forum.com/t/pagination-caching/125818/5 "2008-01-13T17:10:32Z")
On 13 Jan 2008, at 14:18, Luma wrote: > There is an index on category and float\_rating. I’m trying to improve > the action itself. My approach is counting the results first, and > using :offset and :limit in the find-query. It’s only a bit faster. Is > there a better way, or can I improve the code? Sorry to insist but is their an index on both category and float rating (and in that order). That’s completely different to having one index on category and one index on float\_rating, and should make a big difference. Fred ---
**Author:** ![luma](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d6d6ee/32.png) [@luma](https://www.ruby-forum.com/u/luma)\ **Post date:** [January 13, 2008, 11:19pm UTC](https://www.ruby-forum.com/t/pagination-caching/125818/6 "2008-01-13T23:19:06Z")
On Jan 12, 4:10 am, Luma [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > The order\_by term (which I really need) slows down the performance. That’s surprising. It’s only sorting 1000 items at most. What is the performance difference with and without the order? If I had this problem with Microsoft SQL Server, I’d find out what query the app was sending to the database, then look at the query’s execution plan for gross inefficiencies. For all we know, your index isn’t even being used. I don’t know if you can look at execution plans in typical Rails databases like MySQL. ///ark ---
**Author:** ![luma](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d6d6ee/32.png) [@luma](https://www.ruby-forum.com/u/luma)\ **Post date:** [January 14, 2008, 6:12pm UTC](https://www.ruby-forum.com/t/pagination-caching/125818/7 "2008-01-14T18:12:51Z")
Hi with order\_by: … [4;35;1mProduct Columns (0.010000) [0m [0mSHOW FIELDS FROM products [0m [4;36;1mSQL (6.549000) [0m [0;1mSELECT count(\*) AS count\_all FROM products WHERE (category = 0) [0m [4;35;1mProduct Load (783.137000) [0m [0mSELECT \* FROM products WHERE (category = 0) ORDER BY float\_rating DESC LIMIT 0, 20 [0m … Completed in 802.56400 (0 reqs/sec) | Rendering: 11.11700 (1%) | DB: 790.03600 (98%) | 200 OK without order\_by: … [4;36;1mProduct Columns (0.010000) [0m [0;1mSHOW FIELDS FROM products [0m [4;35;1mSQL (0.261000) [0m [0mSELECT count(\*) AS count\_all FROM products WHERE (category = 0) [0m [4;36;1mProduct Load (0.130000) [0m [0;1mSELECT \* FROM products WHERE (category = 0) LIMIT 0, 20 [0m … Completed in 1.77300 (0 reqs/sec) | Rendering: 0.54100 (30%) | DB: 0.52100 (29%) | 200 OK I’m using Mysql 5.0, and the following gives some information about the execution: EXPLAIN EXTENDED SELECT \* FROM products WHERE (category = 0) ORDER BY float\_rating DESC LIMIT 0, 20 id select\_type table type possible\_keys key key\_len ref rows Extra 1 SIMPLE products range index1,index2 index2 17 NULL 155568 Using where; Using filesort EXPLAIN EXTENDED SELECT \* FROM products WHERE (category = 0) LIMIT 0, 20 id select\_type table type possible\_keys key key\_len ref rows Extra 1 SIMPLE products range index1,index2 index2 17 NULL 155568 Using where I guess that index2 is taken in both cases. index2 includes (category, float\_rating) in this order. index1 only includes category. I use it in another place. What might be a useful information: The half of the 3 mio entries of the table have category = 0. But does this matter if I’m selecting only the first 18? Martin ---
**Author:** ![luma](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d6d6ee/32.png) [@luma](https://www.ruby-forum.com/u/luma)\ **Post date:** [January 14, 2008, 6:15pm UTC](https://www.ruby-forum.com/t/pagination-caching/125818/8 "2008-01-14T18:15:48Z")
> What might be a useful information: The half of the 3 mio entries of > the table have category = 0. But does this matter if I’m selecting > only the first 18? I mean “only the first 20”. ---
**Author:** ![luma](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/d6d6ee/32.png) [@luma](https://www.ruby-forum.com/u/luma)\ **Post date:** [January 14, 2008, 7:03pm UTC](https://www.ruby-forum.com/t/pagination-caching/125818/9 "2008-01-14T19:03:11Z")
On Jan 14, 9:12 am, Luma [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > id select\_type table type possible\_keys key key\_len ref rows > Extra > 1 SIMPLE products range index1,index2 index2 17 NULL 155568 Using > where OK, the problem is ‘Using filesort.’ From a very brief google, this can be the kiss of death. Unfortunately, I don’t know how to solve this problem, but if someone has solved it, you should be able to find it. ///ark