Rails Ruby on Rails → 2.x

Comments and changes to this ticket

• You flagged this item as spam.
  

  Lee Hambley January 17th, 2009 @ 10:20 PM

  • Tag changed from “:order, activerecord, adapters, random” to “:order, activerecord, adapters, patch, random”

  +1

• You flagged this item as spam.
  

  Adrian Mugnolo February 20th, 2009 @ 06:40 PM

  +1

• 

  DHH February 27th, 2009 @ 01:42 PM

  I like this, but it needs documentation.

• You flagged this item as spam.
  

  Norman Clarke February 27th, 2009 @ 02:34 PM

  OK, I'll submit an updated patch later today.

• You flagged this item as spam.
  

  Norman Clarke February 27th, 2009 @ 05:12 PM

  Attaching new patch against latest master, with documentation.

  • add_random_order.diff 7.3 KB
• 

  Pratik March 9th, 2009 @ 05:59 PM

  • Assigned user set to “Pratik”
  • State changed from “new” to “wontfix”
  • Title changed from “[PATCH] add support for :order => :random in queries” to “Add support for :order => :random in queries”

  ORDER BY RAND() usually have a performance implication. Finding random records doesn't really have a single solution for all the possible scenarios. Also, for the patch to be accepted, it'll need to mess with query caching, which starts to get dirty. It's best to leave this job to a plugin.

  Thanks.

• You flagged this item as spam.
  

  Norman Clarke March 9th, 2009 @ 06:24 PM

  "Performance" is probably the single worst reason to reject this patch; if you have a table with a million users, what's the "higher performance" way of pulling out 10 random records? Have you ever done this? Database engines are optimized for this sort of thing. I have a hard time imagining how you'd do this faster and simpler in straight Ruby.

  This isn't just some border case either, lots of websites do this. The fact that there is no single solution to finding random records should not impede AR offering a solution to a very common usage scenario. Rails never purports to elegantly solve every problem, only the most common ones.

  As regards caching, I don't see the problem with a query that includes ORDER BY RAND() returning the same dataset if it is performed two times during the same request. This is what would happen if you just enter the query in manually right now without my patch. What's the problem?

  In active record there's an existing pattern of deferring adapter-specific syntax to the adapters. This patch simply extends this to cover the "ORDER BY" clause as well. In that sense it actually removes something exceptional from the existing code.

  Maybe I'm misunderstading something but I find your explantion puzzing.

• 

  Pratik March 9th, 2009 @ 07:00 PM

  Here are some benchmarks with 100k rows ( forget millions ).

  
  [lifo@null]$ script/performance/benchmarker 1 'Item.all(:limit => 5, :order => "rand()")'
              user     system      total        real
  #1      0.000000   0.000000   0.000000 (  4.020854)
  

  Here's a relevant thread - http://groups.google.com/group/r...

• You flagged this item as spam.
  

  Norman Clarke March 9th, 2009 @ 07:14 PM

  Limiting that to selecting only an indexed column would cut down your query time significantly. On my system, I get these times querying against a table with lots of columns and about 100k rows:

  
  SELECT * FROM hits ORDER BY RAND() LIMIT 10;
  

  -> 2.19 seconds

  
  SELECT id FROM hits ORDER BY RAND() LIMIT 10;
  

  -> 0.27 seconds

  Let's say you have a table with not so many rows, then you can simply use :order => :random without thinking about it.

  If you have a table with lots of rows, then OK, you will at some point have to optimize your queries. This is the same as many of other things you do with SQL, not just ORDER BY RANDOM.

• 

  Michael Koziarski March 9th, 2009 @ 08:36 PM

  I agree with pratik here, the RAND() in the order forces a table scan for EVERY query, doing this by default is far too dangerous.

  If you want to efficiently select random records something like this will work:

  
  module ActiveRecord
    class Base
      def self.random
        if (c = count) != 0
          find(:first, :offset =>rand(c))
        end
      end
    end
  end
  

  Even then the performance with large offsets sucks.

• You flagged this item as spam.
  

  Norman Clarke March 9th, 2009 @ 08:45 PM

  Koz, that's not true when you select only indexed columns:

  
  mysql> explain select id from hits order by rand()\G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: hits
           type: index
  possible_keys: NULL
            key: PRIMARY
        key_len: 4
            ref: NULL
           rows: 122869
          Extra: Using index; Using temporary; Using filesort
  1 row in set (0.00 sec)
  

  If you you are querying against a small dataset you could simply use the :order => :random as is. This would likely cover a very significant percentage of the actual use cases, should you put this patch in Rails.

  Otherwise for large datasets an easy optimization would be to build your result set by :select-ing only the primary key, then loop through the result set and use reload to get the model's other fields. Something like:

  
  @users = User.all(:select => :id, :order => :random, :limit => 5)
  @users.each { |u| u.reload}
  

  Pulling out only the fields you need using :select is usually one of the first database optimizations people tend to make anyway.

• 

  Michael Koziarski March 9th, 2009 @ 09:17 PM

  With a sufficiently large table even an index scan will be painful. And to add a feature that's only safe to use if you have a :select with a covering index or a tiny table just doesn't seem worth the risk.

  However having said all this, it's just about adding syntactic sugar to turn

  
   :order=>"rand()"
  

  Into

  
  :order=>:random
  

  The risks here outweigh the benefits for now.

• You flagged this item as spam.
  

  Norman Clarke March 9th, 2009 @ 09:22 PM

  I'm really surprised you would reject something because "with large datasets and no optimization it could create slow queries." I mean, isn't that true for a lot of things? And isn't this going to be used by people who are just going to use RAND() or RANDOM() anyway? What exactly are you protecting people from?

  And it's NOT just syntactic sugar. The whole point of the patch is to make it possible to use the same syntax across database adapters. MySQL uses a different syntax than SQLite and PostgreSQL, which means if you develop and test on SQLite and deploy on MySQL then you experience annoyance and pain.

  Anyway it's your guys' baby so no problem - I guess I just don't see things the same way.

• You flagged this item as spam.
  

  Max Lapshin April 20th, 2009 @ 05:22 PM

  Norman. I advise you to add function RAND into postgresql and RAND into sqlite. And I'm also against including this fix into Rails. Use Postgres to make workaround.

• You flagged this item as spam.
  

  Norman Clarke April 20th, 2009 @ 06:19 PM

  Max, I think most people working with Rails would rather install a plugin than mess around with PG's stored procedure languages or program SQLite extensions in C.

• You flagged this item as spam.
  

  grosser May 18th, 2010 @ 06:19 AM

  another alternative:
  random_records http://github.com/grosser/random_records makes multiple random records available, by fetching them in user-defined random chunks

• You flagged this item as spam.
  

  Norman Clarke May 18th, 2010 @ 02:59 PM

  @grosser

  Well, performance seems to be the thing that keeps coming up with this issue. None of the Ruby solutions I've ever seen for this problem perform as well as an optimized query. Which, if you stop and think about it for a second, is not in the least bit surprising. I guess I have to shout it from a rooftop to make people believe it, but, MySQL's RAND is fast when selecting a single indexed column. Basically, something like:

  ids = User.find(:select => :id, :order => "RANDOM()" ...).to_s(:db)
  users = User.find(ids)
  

  Will perform well with both large and small datasets; no need for any plugin. The example given higher up in this thread ran in .27 seconds against 120k rows on my ancient Macbook.

  If this patch had been accepted, you'd have an easy way to make it work across any database adapter too, so if you test on SQLite and deploy on MySQL, it would work.

  As it stands, if you want to use it everywhere you have to look at

  ActiveRecord::Base.connection.adapter_name
  

  in order to use RAND rather than RANDOM for MySQL.

  I'm still surprised that this patch was never added because "with a sufficiently large table even an index scan will be painful" - that can be said of any query - or that it was called "syntactic sugar", when in fact its point was to add to the adapter a needed abstraction for a syntax which is in wide use.

• You flagged this item as spam.
  

  mikeymicrophone June 19th, 2010 @ 04:23 AM

  Norman, I wish I could use this! For us mere mortals who want to use databases, and not marry them, this is a major issue.

  Koz, in your first comment you indicated that this patch would slow down queries that were not querying for random content (as well as those that were). Am I interpreting you correctly?

  I want to submit for consideration the possibility that randomization is a very important issue for many reasons - partly the fun and usability, and even the efficiency and arresting quality, of apps, and partly the perennial dumbing-down of our culture that has resulted from just reading the first page of google results, and just reading the front page of nytimes instead of browsing through the entire issue. A persistent ordering not only obscures many good answers and feeds back into itself to bury them even deeper; it also gives lay-people a false sense of authority and helps them forget to ask questions.

  A big part of why Rails has always been a high-productivity environment is the instant gratification. As in, things change. I believe it is easier to stay motivated when you make small accommodations in your environment that add to the veracity of your sample data. You just don't want to see the same five things pop up every day - it feels like no progress is being made.

  That said, here is a 10-line solution for others to use.

  module Random
    def db_random
      case ActiveRecord::Base.connection.adapter_name
      when 'MySQL'
        'rand()'
      when 'SQLite'
        'random()'
      end
    end
  end
  

  Then you can do this.

  extend Random
  named_scope :randomized, :order => db_random
  

• You flagged this item as spam.
  

  Norman Clarke June 19th, 2010 @ 04:46 AM

  To be fair to Koz and Pratik, I've looked into this issue some more and despite my earlier insistence, I now think they're more right than I was giving them credit for. With tables of 100,000 records or so, RAND/RANDOM is "probably fast enough" but it does break down quite a bit as tables grow.

  I still do think that this would be a useful patch, but I just want to back off on my earlier claims that this was "fast", because I was mistaken.

• 

  Ryan Bigg October 9th, 2010 @ 10:14 PM

  • Tag cleared.

  Automatic cleanup of spam.

• 

  Ryan Bigg October 21st, 2010 @ 03:36 AM

  Automatic cleanup of spam.

• 

  Jeff Kreeftmeijer October 24th, 2010 @ 01:10 PM

  Automatic cleanup of spam.

• 

  Jeff Kreeftmeijer October 24th, 2010 @ 01:12 PM

  Automatic cleanup of spam.

• 

  Jeff Kreeftmeijer October 24th, 2010 @ 01:12 PM

  Automatic cleanup of spam.

• You flagged this item as spam.
  

  bingbing March 26th, 2011 @ 01:16 AM

  luxury watch

• You flagged this item as spam.
  

  links london April 1st, 2011 @ 09:29 AM

  links of london

• You flagged this item as spam.
  

  yooo April 15th, 2011 @ 09:33 AM

  For those of you who are purchasing Titanium Jewelry or Titanium Necklaces, we realize that this is one of the most important purchasing decisions you will make.

• You flagged this item as spam.
  

  dswllxy April 26th, 2011 @ 09:44 AM

  To be fair to Koz and Pratik, beats by dre I've looked into this issue some more and despite my earlier insistence, monster beatsI now think they're more right than I was giving them credit for. beats headphoneWith tables of 100,000 records or so, beats studioRAND/RANDOM is "probably fast enough" but it does break down quite a bit as tables grow.

  I still do think that this would be a useful patch, but I just want to back off on my earlier claims that this was "fast", powerbeatsbecause I was mistaken.