Pagination caching

Rails
1

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

2

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?

3

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. [email protected]

4

hi Fred,

The index really includes (category, float_rating) in that order.
Indeed, I don’t really know how indexes work :wink:

Martin

On 13 Jan., 17:09, Frederick C. [email protected]

5

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

6

On Jan 12, 4:10 am, Luma [email protected] 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

7

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

8

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”.

9

On Jan 14, 9:12 am, Luma [email protected] 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