Re: Age old order by rand() issue

g USING (aka =) rather than >= . You were also doing a WHERE clause on that could have removed the random result. My only problem with what you are using is that it is more likely to give a large results than a small one. Take a look at the http://jan.kneschke.de/projects/mysql/order-by-rand/

Re: Age old order by rand() issue

I have seen nicer fast random row implement, but that will work. Do you happen to have a snip of it, the one I have seems to lean pretty heavy as far as I can tell, and on occasion, though rare, also sends me an empty result set. -- Scott [EMAIL PROTECTED] -- MySQL General Mailing List F

Re: Age old order by rand() issue

> SELECT storage_path, image_md5, id > FROM images > JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved = > 1)) AS id) AS r2 USING (id) >WHERE approved = 1; > > I really do not get this, SELECT CEIL(RAND() will always return 1 will it > not? Any idea why I get an empty

Re: Age old order by rand() issue

Hi, Responses inline On Thu, May 1, 2008 at 3:11 PM, Scott Haneda <[EMAIL PROTECTED]> wrote: > List search seems to return 0 results, and I am a bit stumped. > > Getting a more optimized order by random with 1 record... > I found a snip online that works, but seems to return an empty on occa

Age old order by rand() issue

List search seems to return 0 results, and I am a bit stumped. Getting a more optimized order by random with 1 record... I found a snip online that works, but seems to return an empty on occasion, and I am not sure why: SELECT storage_path, image_md5, id FROM images JOIN (SELECT CEIL(RAND(

implementation of SELECT ... ORDER BY RAND() LIMIT 1

2007/2/7, Jos Elkink <[EMAIL PROTECTED]>: Hi all, I have a question about the combination of RAND and LIMIT 1. If I have a query like: SELECT ... ORDER BY RAND() LIMIT 1 with the ... replaced with a normal query on one table. How is this implemented? Is this optimized for the fact t

Re: implementation of SELECT ... ORDER BY RAND() LIMIT 1

I have a question about the combination of RAND and LIMIT 1. If I have a query like: SELECT ... ORDER BY RAND() LIMIT 1 with the ... replaced with a normal query on one table. How is this implemented? Is this optimized for the fact that it only needs one entry? Try prefixing your query with

implementation of SELECT ... ORDER BY RAND() LIMIT 1

Hi all, I have a question about the combination of RAND and LIMIT 1. If I have a query like: SELECT ... ORDER BY RAND() LIMIT 1 with the ... replaced with a normal query on one table. How is this implemented? Is this optimized for the fact that it only needs one entry? And what about when

RE: ORDER BY RAND() gives me duplicate rows sometimes

The SELECT that Daevid originally tried is straight out of the documentation, which says that ORDER BY RAND() LIMIT x is a good way to get a random sample of your data. The documentation also says you can't use a RAND() column in an ORDER BY clause because the ORDER BY would evaluate the c

Re: ORDER BY RAND() gives me duplicate rows sometimes

Add DISTINCT(primary_key) in your query? Regards Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY RAND() gives me duplicate rows sometimes

On 2006-11-09 Daevid Vincent wrote: > I am using this query to pull three random comments from a table: > > "SELECT *, DATE_FORMAT(created_on, '%b %D') as date_format FROM comments > ORDER BY RAND() LIMIT 3"; > > The problem is that sometimes, I get two o

ORDER BY RAND() gives me duplicate rows sometimes

I am using this query to pull three random comments from a table: "SELECT *, DATE_FORMAT(created_on, '%b %D') as date_format FROM comments ORDER BY RAND() LIMIT 3"; The problem is that sometimes, I get two of the same comment. How can I refine this query to give me 3

Re: Making ORDER BY RAND() more random [resend]

In the below query, the results first row is usually the same 3-4 tracks out of 30. so should I use PHP to generate a random seed, $r, and pass that to the query ? ORDER BY RAND($r) or, can it be done just with mysql many thanks g On Oct 3, 2005, at 10:15 AM, Michael Stassen wrote: Graham

Re: Making ORDER BY RAND() more random [resend]

Graham Anderson wrote: is there a way to make ORDER BY RAND() a bit more spontaneous ? I am using it in a php statement: $sql = "SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.picture, media.spani

Re: Making ORDER BY RAND() more random [resend]

change the seed on the random function. http://dev.mysql.com/doc/mysql/en/mathematical-functions.html On Mon, 2005-10-03 at 08:33 -0700, Graham Anderson wrote: > is there a way to make ORDER BY RAND() at bit more spontaneous ? > > I am using it in a php statement: > $sql = &quo

Making ORDER BY RAND() more random [resend]

is there a way to make ORDER BY RAND() at bit more spontaneous ? I am using it in a php statement: $sql = "SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.pi

Making ORDER BY RAND() more random

is there a way to make ORDER BY RAND() at bit more spontaneous ? I am using it in a php statement: $sql = "SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.pi

Re: ORDER BY rand()

Craig Hoffman wrote: Hey Folks, I have a query where it pulls random data and display's it. SELECT route_photo, route, route_count, area FROM routes WHERE ???? ORDER BY RAND() LIMIT 1 The query works fine, however, the "route_photo" field is partially populated. This results i

ORDER BY rand()

Hey Folks, I have a query where it pulls random data and display's it. SELECT route_photo, route, route_count, area FROM routes WHERE ???? ORDER BY RAND() LIMIT 1 The query works fine, however, the "route_photo" field is partially populated. This results in just a "route&qu

Repost: Order by RAND + join problem

Hello list, im having a hughe problem with the RAND() function first of all im using mysql 3.23 so subquerys are impossible. Im having three tables which are joined by ID's now i want to select a single row random out of the join set whats the best way to do it? My Table structure is: table1

Re: group by & order by rand() problem

Alle 21:57, giovedì 1 aprile 2004, Michael Stassen ha scritto: > You could probably accomplish this with a variant of the MAX-CONCAT > trick > . > Something like: > >SELECT user_id, > SUBSTRING(MAX(CONCAT(TRUNCATE(RAND

Re: group by & order by rand() problem

1 | second banner | | 4 | 2 | forth banner | ++-+---+ etc... I have tried with following query but the banner doesn't change while multiple calls: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); Can anyone help me? Thanks in advance, Alex -- MySQL

RE: group by & order by rand() problem

-Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: Thursday, April 01, 2004 7:16 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: group by & order by rand() problem Try seeding your rand. > -Original Message- > From: [EMAIL PROTECTED] [mai

RE: group by & order by rand() problem

> Try seeding your rand. Tried. It doesn't work. The select shows always the same records but in different order: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); first call ++-+---+ | id | user_id | title | ++-+---+ | 1 |

RE: group by & order by rand() problem

Try seeding your rand. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 31, 2004 12:57 PM > To: [EMAIL PROTECTED] > Subject: group by & order by rand() problem > > I have this table: > &g

group by & order by rand() problem

the banner doesn't change while multiple calls: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); Can anyone help me? Thanks in advance, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY RAND() performance

extras, if say #20 is not available. There are many options, we have even in some cases, created a table and run the order by rand query every 5 minutes and just have it update a table. And then we just do a select from that secondary table. So every 5 minutes you have new random items. Another way is

Re: ORDER BY RAND() performance

ly want 30. That way, you can have extras, > if say #20 is not available. > > There are many options, we have even in some cases, created a table and run > the order by rand query every 5 minutes and just have it update a table. > And then we just do a select from that secondary table

RE: ORDER BY RAND() performance

available. There are many options, we have even in some cases, created a table and run the order by rand query every 5 minutes and just have it update a table. And then we just do a select from that secondary table. So every 5 minutes you have new random items. Donny > -Original Mess

Re: ORDER BY RAND() performance

only be used for quick lookups in random selects anyway. Then, if record 6 is removed, record 7 would become record 6, record 8 would now be 7 and so on. I know you can maintain this sort of thing yourself, but it takes work to maintain consistency and it would be a nice feature to have. If this was

Re: ORDER BY RAND() performance

Ray wrote: > an alternative to the order by rand() with large record sets is to > pick a random starting point "limit $randPoint, 30" don't know if > its a viable solution to your situation, but it limits you to 2 > querys (row count, fetch) rather then the 30 (fetch

RE: ORDER BY RAND() performance

just do a limit 19345, 1 or something like that. We have tried another option which we stopped using which was creating 30 random numbers and then doing 30 select statements. But that was slower overall than 1 select with order by rand. One other option that we use sometimes is say you need

Re: ORDER BY RAND() performance

AND() slowness. I have been testing with a > separate table that ONLY contains the id of the pics, and as it > grows toward 100,000 records this simple query does get noticeably > slower: > > SELECT * FROM visible_pics ORDER BY RAND() LIMIT 30; > > Where visible_pics just has two nu

RE: ORDER BY RAND() performance

Mon, 8 Mar 2004, Donny Simonton wrote: > ORDER BY RAND(), just sucks in my opinion. We have created our own internal > randomization system because pretty much everytime you use it will show up > in the slow query log, because of the using temporary, using filesort it > does. Splitti

RE: ORDER BY RAND() performance

ORDER BY RAND(), just sucks in my opinion. We have created our own internal randomization system because pretty much everytime you use it will show up in the slow query log, because of the using temporary, using filesort it does. Splitting your data into a hundred tables will still make it

Re: ORDER BY RAND() performance

and as it grows toward 100,000 records this simple query does get noticeably slower: SELECT * FROM visible_pics ORDER BY RAND() LIMIT 30; Where visible_pics just has two numeric ID fields (pic_id and doc_id). It doesn't seem to matter if I make pic_id a primary key or not. I think I've redu

Re: ORDER BY RAND() performance

oticed that the performance of "ORDER BY RAND()" on > this table has a significant impact on performace. I have all the > relevant indexes defined, and I have researched this issue on the Web. > It seems that other people have also encountered a performance hit while > using ORDE

ORDER BY RAND() performance

Hi all, I am using MySQL 4.0.x to run a community website which has (among other things) over 19,000 pictures. There is a page that selects 30 random thumbnails. I have noticed that the performance of "ORDER BY RAND()" on this table has a significant impact on performace. I have all th

order by rand()

Hey all... I did a quick check of the archives, but didn't see anything recent about this... I use PHP and mySQL, and am supposed to be able to randomize the order in which the rows are held when retrieved using ORDER BY RAND(). This isn't working for me... it's on a third-pa

Re: ORDER BY RAND() not working

ER BY MyOrder; hth, Arthur - Original Message - From: "Jamie Tibbetts" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, July 11, 2002 1:14 PM Subject: Re: ORDER BY RAND() not working > > $id = mysql_query("SELECT * FROM products WHERE sale_price

RE: ORDER BY RAND() not working

[snip] PHP code: $result = mysql_query("SELECT * FROM products WHERE sale_price IS NOT NULL ORDER BY RAND() LIMIT 3") or die(mysql_error()); The error message is as follows: You have an error in your SQL syntax near 'RAND() LIMIT 3' at line 1 [/snip] Using; $query = &

Re: ORDER BY RAND() not working

> $id = mysql_query("SELECT * FROM products WHERE sale_price IS NOT NULL ORDER > BY RAND() LIMIT 3",$link) or die(mysql_error()); PHP code: $result = mysql_query("SELECT * FROM products WHERE sale_price IS NOT NULL ORDER BY RAND() LIMIT 3") or die(mysql_error()); The e

RE: ORDER BY RAND() not working

What does your PHP code look like ? It might be a syntax problem inside PHP -Message d'origine- De : Jamie Tibbetts [mailto:[EMAIL PROTECTED]] Envoye : jeudi 11 juillet 2002 18:22 A : [EMAIL PROTECTED] Objet : ORDER BY RAND() not working I'm running PHP 4.1.2 and MySQL

RE: ORDER BY RAND() not working

[snip] I'm running PHP 4.1.2 and MySQL 3.23.39. I have a simple query that works if I telnet into MySQL and run the query manually. If I try and use it in a PHP page, I get the "Supplied argument is not a valid MySQL result resource" error. However, if I take out the ORDER BY RAND(

ORDER BY RAND() not working

I'm running PHP 4.1.2 and MySQL 3.23.39. I have a simple query that works if I telnet into MySQL and run the query manually. If I try and use it in a PHP page, I get the "Supplied argument is not a valid MySQL result resource" error. However, if I take out the ORDER BY RAND() part

Performance of ORDER BY RAND()

I'm seeing queries a little bit slower than I'd like when I try to get them in random order, by using ORDER BY RAND(). It seems like mysql should be able to do this quickly, especially in the simple case of: SELECT * FROM db ORDER BY RAND() LIMIT 1 , where it could choose a ran

Re: order by rand() question

>> In a slight change of this question (since I have no data to >> currently test this with, as my ISP is using too old a >> version of mysql), does anyone know what something like >> this would do? >> >> SELECT * FROM theTable ORDER BY RAND(), date LIMI

RE: order by rand() question

ROM theTable ORDER BY RAND() LIMIT 5; SELECT * FROM temptable ORDER BY date; DROP TABLE temptable; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list

RE: order by rand() question

> In a slight change of this question (since I have no data to > currently test this with, as my ISP is using too old a > version of mysql), does anyone know what something like > this would do? > > SELECT * FROM theTable ORDER BY RAND(), date LIMIT 5; Exactly the same as SELE

Re: order by rand() question

In a slight change of this question (since I have no data to currently test this with, as my ISP is using too old a version of mysql), does anyone know what something like this would do? SELECT * FROM theTable ORDER BY RAND(), date LIMIT 5; I'd like to be able to pull out a certain numb

RE: order by rand() question

> I don't think the answer has changed since last week :-) > > IMHO it's not "really slow and inefficient", anyway - this script: (clipped) > produces a table with 1 rows each containing an integer. Then we do: > > mysql> SELECT * FROM mytable ORDER

Re: order by rand() question

where number=1 order by rand() limit 1; > > If 1000 rows would match this command does mysql first find all 1000 rows > and THEN randomly select 1 of those to return, OR does it just randomly > start somewhere and then stop when it finds one? The first method would > be really

RE: order by rand() question

I don't think the answer has changed since last week :-) IMHO it's not "really slow and inefficient", anyway - this script: ---start--- end produces a table with 1 rows each containing an integer. Then we do: mysql> SELECT * FROM mytable

order by rand() question

Hi all, trying to do something and have it be as efficient as possilble. My question is if I have a table with say 10,000 rows in it, and I issue a command like this select * from table where number=1 order by rand() limit 1; If 1000 rows would match this command does mysql first find all

Re: Question about order by rand()

On Wed, Jun 13, 2001 at 03:25:55PM -0600, Matt Heaton wrote: > I can't seem to find a specificatoin on order by rand() and have a question. > > Lets say a table has 10,000 rows in it, and I want to get one row that > matches > > so I do a > > select * from table

Question about order by rand()

I can't seem to find a specificatoin on order by rand() and have a question. Lets say a table has 10,000 rows in it, and I want to get one row that matches so I do a select * from table where number=1 order by rand() limit 1; What if 100 rows match? Does it first get all 100 rows and

ORDER BY RAND()

Hi~ I have inserted 1,000,000 record into table. and selected 1 record using order by rand(). but, it was very very slow. mysql> explain member ; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Ex

Re: SELECT... ORDER BY RAND() LIMIT x FAILS

[EMAIL PROTECTED] writes: > >Description: > The documentation says that I can do: > select * from table order by rand() limit 10 > as an example, however it fails and there is no error message. > I *can* do: > select * from table order by rand() > which works f

SELECT... ORDER BY RAND() LIMIT x FAILS

>Description: The documentation says that I can do: select * from table order by rand() limit 10 as an example, however it fails and there is no error message. I *can* do: select * from table order by rand() which works fine. It just seems that adding the limit clause causes something to br

order by RAND()

I was doing a basic query on my windoze 98 system like so: Select * from table_name order by RAND() LIMIT 1; When i tried to port this to a linux box running mysql 3.22.32 it doesn't seem to work. I thought this was compatible with that version but i guess not. Can anyone recommend an

Re: ORDER BY RAND() Too Slow! Alternatives?

At 11:39 PM 2/10/2001 -0800, Stephen Waits wrote: >Never mind on the "it doesn't work on my system" more like it didn't >work on my brain :) Works fine. Oh, phew. >Theoretically it could be as fast as Carsten's method couldn't it? If >it hit a record on the first shot? Otherwise it's pounding

Re: ORDER BY RAND() Too Slow! Alternatives?

"Jeffrey D. Wheelhouse" wrote: > > SELECT @lines:=COUNT(id) FROM table; > SET @rand=CEILING(RAND()*@lines); > SELECT * FROM table WHERE (@rand:=@rand-1)+id=id; Never mind on the "it doesn't work on my system" more like it didn't work on my brain :) Works fine. And now that I ponder it a bit

Re: ORDER BY RAND() Too Slow! Alternatives?

"Jeffrey D. Wheelhouse" wrote: > > Here's another approach. I'm curious about the performance implications: > > SELECT @lines:=COUNT(id) FROM table; > SET @rand=CEILING(RAND()*@lines); > SELECT * FROM table WHERE (@rand:=@rand-1)+id=id; > > This *should* give each row an equal chance, but it'

RE: ORDER BY RAND() Too Slow! Alternatives?

ot;fair" match without incurring at least one full pass through the table? Jeff At 10:11 PM 2/10/2001 +0100, Carsten H. Pedersen wrote: > > Hi there, > > > > In the quest to get a random row from a table, "order by rand()" has > > proven too inefficient and

RE: ORDER BY RAND() Too Slow! Alternatives?

(702)656-1608 -Original Message- From: Stephen Waits [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 10, 2001 12:13 PM To: [EMAIL PROTECTED] Subject: ORDER BY RAND() Too Slow! Alternatives? Hi there, In the quest to get a random row from a table, "order by rand()" has prov

RE: ORDER BY RAND() Too Slow! Alternatives?

> Hi there, > > In the quest to get a random row from a table, "order by rand()" has > proven too inefficient and slow. It's slow because MySQL apparently > selects ALL rows into memory, then randomly shuffles ALL of them, then > gives you the first one - very in

Re: ORDER BY RAND() Too Slow! Alternatives?

Could you do something like: CREATE TEMPORARY TABLE temptable ( pk INTEGER, rand INTEGER ); INSERT INTO temptable SELECT yourpk,Rand() FROM yourtable; SELECT yourtable.* FROM yourtable,temptable WHERE pk=yourpk ORDER BY rand; DROP TABLE temptable; That might be quicker than your current

ORDER BY RAND() Too Slow! Alternatives?

Hi there, In the quest to get a random row from a table, "order by rand()" has proven too inefficient and slow. It's slow because MySQL apparently selects ALL rows into memory, then randomly shuffles ALL of them, then gives you the first one - very inefficient. There are a few