[development] Drupal 7 Entities - view random node

[Peter Droogmans]

My 2 cents,

Use something like this: 

SELECT * FROM node WHERE type = 'quote' AND nid <= FLOOR(RAND() * (SELECT MAX(nid) FROM node)) LIMIT 1;

It isn't truly random, because it depends on how the nodes you're looking for are distributed, but it is faster than sort by rand().You can always extend the subquery with the same WHERE as the parent, but it will slow down the whole thing.


Best regards,
Peter Droogmans
Attiks
Ketsstraat 94
2140 Borgerhout
Belgium
32 3 288 61 17
32 497 44 44 77



-----Original Message-----
From: development-bounces at drupal.org [mailto:development-bounces at drupal.org] On Behalf Of Larry Garfield
Sent: maandag 24 januari 2011 3:33 AM
To: development at drupal.org
Subject: Re: [development] Drupal 7 Entities - view random node

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