[development] Drupal 7 Entities - view random node

[Larry Garfield]

The only way I can see for a separate lookup table to help would be to 
prepopulate it with your desired filters and an arbitrary autoinc field.  So 
you would do something like:

INSERT INTO {mytemp} SELECT nid FROM {node} WHERE type='quote';

And then rebuild that (incrementally or full) every time you add or remove a 
quote node.  Then to select, you would select one int at random between 1 and 
COUNT(*) in mytemp, then pull that record.

The above makes the actual "fetch one at random" operation almost O(1), but 
you need to create and maintain that 

Actually, I just found this in the MySQL manual in user comments:

SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM {mytemp};
SELECT nid FROM {mytemp} LIMIT $rand_row, 1;

That could actually be faster, at the cost of being two queries so there are 
technically potential race conditions.  (Unlikely to be a problem in practice, 
but still there.)  I've not tried the above; I just saw it and thought it was 
cool. :-)  (It's also MySQL-specific. I don't know if the same thing would 
work well on other databases.)

The MySQL manual has a couple of other suggestions in user comments, too:
http://dev.mysql.com/doc/refman/5.0/en/select.html

--Larry Garfield

On Sunday, January 23, 2011 8:12:08 pm nan wich wrote:
> Thanks, Larry, that make me feel a bit better.
> 
> The suggestion is to keep a separate table of applicable nodes, generate a
> random number from the number of records, and get that record. Your
> explanation seems to indicate that one is simply spreading the "overhead"
> out over time - and probably increasing it in total, maybe. Given that the
> block can be refreshed on every page load, and a new node
> created relatively infrequently, I can also see that there may be some
> merit when the number of nodes gets fairly large. Maybe something like
> "SELECT n.* FROM {quotes_random} r INNER JOIN {node} n ON n.nid = r.nid
> WHERE r.row_num = 1 + RAND() * (SELECT MAX(row_num) FROM
> {quotes_random})". (Sorry for the D6 version; it would take me a much
> longer time to construct in D7.)
> 
> 
> Nancy
>  
> Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L.
> King, Jr.
> 
> 
> 
> ________________________________
> 
> From: Larry Garfield
> I don't know that I've ever tried it with 12k nodes so I can't say from
> experience.
> 
> Randomization is a tricky subject.  The common method, which Views uses as
> does the ->orderRandom() method of DBTNG, boils down to adding a random
> number as a new column for each record, ordering by that, and then (in
> this case) discarding all but the first record.  That involves a
> linear-growth creation for the new column (a fixed additional cost per
> record) and then an integer ordering.  Assuming the sorting function in
> the SQL database is sane (which if it isn't you need a new database), that
> should be an n*log n algorithm. (That's as fast as a sorting algorithm can
> get.)