<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:12pt"><DIV></DIV>
<DIV>Thanks, Peter. That's pretty much the same query as in Ed Rackham's post. My concern that selecting directly from the node table is that randomness would be compromised by the generally low volume of the one content type for most users. I'm going to try to create a simple installation to test this.<BR> </DIV>
<P><FONT color=#ff007f size=4 face="bookman old style, new york, times, serif"><EM><STRONG>Nancy</STRONG></EM></FONT></P>
<P> </P>
<P><FONT face="arial, helvetica, sans-serif">Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.</FONT></P>
<DIV><BR></DIV>
<DIV style="FONT-FAMILY: arial, helvetica, sans-serif; FONT-SIZE: 12pt"><BR>
<DIV style="FONT-FAMILY: Courier New, monaco, monospace, sans-serif; FONT-SIZE: 10pt"><FONT size=2 face=Tahoma>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Peter Droogmans <Peter@attiks.com><BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> "development@drupal.org" <development@drupal.org><BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Mon, January 24, 2011 3:28:32 AM<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: [development] Drupal 7 Entities - view random node<BR></FONT><BR>My 2 cents,<BR><BR>Use something like this: <BR><BR>SELECT * FROM node WHERE type = 'quote' AND nid <= FLOOR(RAND() * (SELECT MAX(nid) FROM node)) LIMIT 1;<BR><BR>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.<BR><BR><BR>Best regards,<BR>Peter Droogmans<BR>Attiks<BR>Ketsstraat 94<BR>2140 Borgerhout<BR>Belgium<BR>32 3 288 61 17<BR>32 497 44 44
77<BR><BR><BR><BR>-----Original Message-----<BR>From: <A href="mailto:development-bounces@drupal.org" ymailto="mailto:development-bounces@drupal.org">development-bounces@drupal.org</A> [mailto:<A href="mailto:development-bounces@drupal.org" ymailto="mailto:development-bounces@drupal.org">development-bounces@drupal.org</A>] On Behalf Of Larry Garfield<BR>Sent: maandag 24 januari 2011 3:33 AM<BR>To: <A href="mailto:development@drupal.org" ymailto="mailto:development@drupal.org">development@drupal.org</A><BR>Subject: Re: [development] Drupal 7 Entities - view random node<BR><BR>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:<BR><BR>INSERT INTO {mytemp} SELECT nid FROM {node} WHERE type='quote';<BR><BR>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<BR>COUNT(*) in mytemp, then pull that record.<BR><BR>The above makes the actual "fetch one at random" operation almost O(1), but you need to create and maintain that <BR><BR>Actually, I just found this in the MySQL manual in user comments:<BR><BR>SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM {mytemp}; SELECT nid FROM {mytemp} LIMIT $rand_row, 1;<BR><BR>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.)<BR><BR>The MySQL manual has a couple of other suggestions in user comments, too:<BR><A href="http://dev.mysql.com/doc/refman/5.0/en/select.html" target=_blank>http://dev.mysql.com/doc/refman/5.0/en/select.html
</A><BR><BR>--Larry Garfield<BR><BR>On Sunday, January 23, 2011 8:12:08 pm nan wich wrote:<BR>> Thanks, Larry, that make me feel a bit better.<BR>> <BR>> The suggestion is to keep a separate table of applicable nodes, <BR>> generate a random number from the number of records, and get that <BR>> record. Your explanation seems to indicate that one is simply spreading the "overhead"<BR>> out over time - and probably increasing it in total, maybe. Given that <BR>> the block can be refreshed on every page load, and a new node created <BR>> relatively infrequently, I can also see that there may be some merit <BR>> when the number of nodes gets fairly large. Maybe something like <BR>> "SELECT n.* FROM {quotes_random} r INNER JOIN {node} n ON n.nid = <BR>> r.nid WHERE r.row_num = 1 + RAND() * (SELECT MAX(row_num) FROM <BR>> {quotes_random})". (Sorry for the D6 version; it would take me a much <BR>> longer time to construct
in D7.)<BR>> <BR>> <BR>> Nancy<BR>> <BR>> Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L.<BR>> King, Jr.<BR>> <BR>> <BR>> <BR>> ________________________________<BR>> <BR>> From: Larry Garfield<BR>> I don't know that I've ever tried it with 12k nodes so I can't say <BR>> from experience.<BR>> <BR>> Randomization is a tricky subject. The common method, which Views <BR>> uses as does the ->orderRandom() method of DBTNG, boils down to adding <BR>> a random number as a new column for each record, ordering by that, and <BR>> then (in this case) discarding all but the first record. That <BR>> involves a linear-growth creation for the new column (a fixed <BR>> additional cost per<BR>> record) and then an integer ordering. Assuming the sorting function <BR>> in the SQL database is sane (which if it isn't you need a new <BR>> database), that
should be an n*log n algorithm. (That's as fast as a <BR>> sorting algorithm can<BR>> get.)<BR><BR></DIV></DIV></div></body></html>