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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On 12 Jan 2008, at 12:10, Luma wrote:> > 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.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?> > Thanks > > Luma > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On 12 Jan 2008, at 12:10, Luma wrote:
>
>
>
> > 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.
>
> 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?
>
> > Thanks
>
> > Luma- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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> > 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 Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: >> On 12 Jan 2008, at 12:10, Luma wrote: >> >> >> >>> 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. >> >> 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? >> >>> Thanks >> >>> Luma- Zitierten Text ausblenden - >> >> - Zitierten Text anzeigen - > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 > > > > > > > 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 Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > wrote: > >> On 12 Jan 2008, at 12:10, Luma wrote: > > >>> 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. > > >> 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? > > >>> Thanks > > >>> Luma- Zitierten Text ausblenden - > > >> - Zitierten Text anzeigen -- Zitierten Text ausblenden - > > - Zitierten Text anzeigen ---~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Jan 12, 4:10 am, Luma <martin....-hi6Y0CQ0nG0@public.gmane.org> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 On 13 Jan., 23:18, Mark Wilden <m...-OCn100epQuBBDgjK7y7TUQ@public.gmane.org> wrote:> On Jan 12, 4:10 am, Luma <martin....-hi6Y0CQ0nG0@public.gmane.org> 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--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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". --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Jan 14, 9:12 am, Luma <martin....-hi6Y0CQ0nG0@public.gmane.org> wrote:> 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 > whereOK, 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---