Re: Age old order by rand() issue

[Rob Wultsch]

On Fri, May 2, 2008 at 6:58 AM, Scott Haneda <[EMAIL PROTECTED]> wrote:
>
> > 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]
>

You should not be getting empty results with the second/third query.
The reason you were (probably) previously getting empty results with
the first query was because you were doing the join using 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/

You probably do not need this.
If you have a large data set, you probably don't want this.

-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Age old order by rand() issue

[Scott Haneda]

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
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Age old order by rand() issue

[Rob Wultsch]

>  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 result set at times?

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand
So CEIL(RAND() ) would always be 1.
My guess is you have is that you have hole(s) in your data set.

For the record you are not doing CEIL(RAND() ), your doing CEIL(RAND()
* (SELECT MAX(id) FROM images WHERE approved => 1))

-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Age old order by rand() issue

[Rob Wultsch]

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 occasion,
> and I am not sure why:
>
>  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 result set at times?

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand
So CEIL(RAND() ) would always be 1.
My guess is you have is that you have hole(s) in your data set.

>
>  I then managed to rig this together:
>  SELECT * FROM images AS t
>   JOIN (SELECT CEIL(MAX(id)*RAND()) AS id
>   FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1;
>
>  This works, but I get an odd result, in that the id column is listed twice,
> once at the beginning, where it is in the table, and once at the end.
> Duplicate values of course.

Using a star is less than a great idea. You are a second id from
CEIL(MAX(id)*RAND()) AS id being joined in.
Specify the columns you want...

>  If I narrow the * to a real called select, such as
>  SELECT id, storage_path, image_md5 FROM images AS t
>   JOIN (SELECT CEIL(MAX(id)*RAND()) AS id
>   FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1;
>
> ->   FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1;
>  ERROR 1052 (23000): Column 'id' in field list is ambiguous
Mysql wants you to specify what table you want the id from. Meaning
from x or t...
SELECT t.id, storage_path, image_md5 FROM images AS t
JOIN (SELECT CEIL(MAX(id)*RAND()) AS id
FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1;

I have seen nicer fast random row implement, but that will work.

-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Age old order by rand() issue

[Scott Haneda]

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() * (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 result set at times?


I then managed to rig this together:
SELECT * FROM images AS t
 JOIN (SELECT CEIL(MAX(id)*RAND()) AS id
  FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1;

This works, but I get an odd result, in that the id column is listed  
twice, once at the beginning, where it is in the table, and once at  
the end.  Duplicate values of course.


If I narrow the * to a real called select, such as
SELECT id, storage_path, image_md5 FROM images AS t
 JOIN (SELECT CEIL(MAX(id)*RAND()) AS id
  FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1;

->   FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1;
ERROR 1052 (23000): Column 'id' in field list is ambiguous

I can not seem to get past that error, and would like to call just the  
columns I want.  Or, if someone has a better random record return  
select, I am all ears.


Thanks so much.

--
Scott
[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[Jan Pieter Kunst]

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 that it only needs one
entry?

And what about when there is a combination of tables

SELECT a.a, b.b FROM a,b WHERE a.b = b.id ORDER BY RAND() LIMIT 1

And in the case of

SELECT a.a, b.b FROM a LEFT JOIN b ON a.b = b.id ORDER BY RAND() LIMIT 1

Some say that especially in the last two cases, it is faster to just
retrieve the entire list and then select randomly.

And what if the case is that the limit is larger than 1, but smaller
than the entire table?

I am asking because we have various of these queries in our code and
serious issues with speed, and I was wondering whether I am assuming
optimization in the mysql code where they don't actually exist.

Any help on this would be much appreciated.


I just  dealt with this problem myself. The problem as far as I
understand it is that ORDER BY RAND() LIMIT 1 does a full table scan.

I found the solution and a serious speedup in the comments on this page:

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

HTH,
Jan Pieter

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[Philip Hallstrom]

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 "EXPLAIN" and see what it says it's going to 
do.  Pretty sure it's going to look at *every* row in the table, compute a 
random value, sort it, then return the first one.


So, for a table with a good number of rows, the above is going to be 
horrificly inefficient.  It would be a lot faster to do something like:


rowcount = select count(*) from table
random_value = something between 0 and rowcount - 1
select ... LIMIT 1 OFFSET random_value

-philip



And what about when there is a combination of tables

SELECT a.a, b.b FROM a,b WHERE a.b = b.id ORDER BY RAND() LIMIT 1

And in the case of

SELECT a.a, b.b FROM a LEFT JOIN b ON a.b = b.id ORDER BY RAND() LIMIT 1

Some say that especially in the last two cases, it is faster to just
retrieve the entire list and then select randomly.

And what if the case is that the limit is larger than 1, but smaller
than the entire table?

I am asking because we have various of these queries in our code and
serious issues with speed, and I was wondering whether I am assuming
optimization in the mysql code where they don't actually exist.

Any help on this would be much appreciated.

Regards,

Jos
http://www.cantr.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[Jos Elkink]

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 there is a combination of tables

SELECT a.a, b.b FROM a,b WHERE a.b = b.id ORDER BY RAND() LIMIT 1

And in the case of

SELECT a.a, b.b FROM a LEFT JOIN b ON a.b = b.id ORDER BY RAND() LIMIT 1

Some say that especially in the last two cases, it is faster to just
retrieve the entire list and then select randomly.

And what if the case is that the limit is larger than 1, but smaller
than the entire table?

I am asking because we have various of these queries in our code and
serious issues with speed, and I was wondering whether I am assuming
optimization in the mysql code where they don't actually exist.

Any help on this would be much appreciated.

Regards,

Jos
http://www.cantr.net

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

[Jerry Schwartz]

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 column multiple times. I'm
not sure what, exactly, the difference is between the two.

In any case, as I understand it your (Christian's) suggestion runs counter
to the documentation.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -Original Message-
> From: Christian Hammers [mailto:[EMAIL PROTECTED]
> Sent: Friday, November 10, 2006 2:57 AM
> To: Daevid Vincent
> Cc: mysql@lists.mysql.com
> Subject: 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 of the same
> comment. How can I
> > refine this query to give me 3 unique/distinct ones?
>
> Maybe
>
> SELECT DISTINCT
>   rand() as rnd,
>   *,
>   DATE_FORMAT(created_on, '%b %D') as date_format
> FROM
>   comments
> ORDER BY
>   rnd
> LIMIT
>   3
> ;
>
> bye,
>
> -christian-
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>




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

[uYe]

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

[Christian Hammers]

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 of the same comment. How can I
> refine this query to give me 3 unique/distinct ones?

Maybe

SELECT DISTINCT 
  rand() as rnd, 
  *, 
  DATE_FORMAT(created_on, '%b %D') as date_format 
FROM 
  comments
ORDER BY 
  rnd
LIMIT 
  3
;

bye,

-christian-

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ORDER BY RAND() gives me duplicate rows sometimes

[Daevid Vincent]

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 unique/distinct ones?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[Graham Anderson]

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 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.spanish as trackName,
media.path,
media.quality,
mediaType.id as mediaType
FROM artist, media, playlistItems, mediaType
WHERE playlistItems.playlist_id = $myID
AND playlistItems.media_id = media.id
AND media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
    ORDER BY RAND() LIMIT 0, 30";
the result seems to be pretty predictable 
is there a way to improve RAND() or is there something better ?
Should I be using php to randomize the found set instead ?
many thanks
g


I don't think anyone can answer this, as is.  RAND() is meant to 
produce a pseudo-random sequence that is not truly random in the 
mathematical sense, but which is usually good enough for what you 
appear to be doing.  The manual 
<http://dev.mysql.com/doc/mysql/en/mathematical-functions.html> puts 
it this way, "RAND() is not meant to be a perfect random generator, 
but instead a fast way to generate ad hoc random numbers that is 
portable between platforms for the same MySQL version."


What is your standard for randomness?  What do you mean by "the result 
seems to be pretty predictable"?  Put another way, what are you 
expecting, and what are you getting?


Michael




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[Michael Stassen]

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.spanish as trackName,
media.path,
media.quality,
mediaType.id as mediaType
FROM artist, media, playlistItems, mediaType
WHERE playlistItems.playlist_id = $myID
AND playlistItems.media_id = media.id
AND media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
    ORDER BY RAND() LIMIT 0, 30";

the result seems to be pretty predictable 
is there a way to improve RAND() or is there something better ?
Should I be using php to randomize the found set instead ?

many thanks
g


I don't think anyone can answer this, as is.  RAND() is meant to produce a 
pseudo-random sequence that is not truly random in the mathematical sense, 
but which is usually good enough for what you appear to be doing.  The 
manual <http://dev.mysql.com/doc/mysql/en/mathematical-functions.html> puts 
it this way, "RAND() is not meant to be a perfect random generator, but 
instead a fast way to generate ad hoc random numbers that is portable 
between platforms for the same MySQL version."


What is your standard for randomness?  What do you mean by "the result seems 
to be pretty predictable"?  Put another way, what are you expecting, and 
what are you getting?


Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[Henry Wong]

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 = "SELECT media.id,
>   artist.name as artist,
>   artist.spanish as bio,
>   artist.purchaseLink,
>   artist.picture,
>   media.spanish as trackName,
>   media.path,
>   media.quality,
>   mediaType.id as mediaType
>   FROM artist, media, playlistItems, mediaType
>   WHERE playlistItems.playlist_id = $myID
>   AND playlistItems.media_id = media.id
>   AND media.artist_id = artist.id
>   AND media.mediaType_id = mediaType.id
>  ORDER BY RAND() LIMIT 0, 30";
> 
> 
> the result seems to be pretty predictable 
> is there a way to improve RAND() or is there something better ?
> Should I be using php to randomize the found set instead ?
> 
> 
> many thanks
> g
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Making ORDER BY RAND() more random [resend]

[Graham Anderson]

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.picture,
media.spanish as trackName,
media.path,
media.quality,
mediaType.id as mediaType
FROM artist, media, playlistItems, mediaType
WHERE playlistItems.playlist_id = $myID
AND playlistItems.media_id = media.id
AND media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
    ORDER BY RAND() LIMIT 0, 30";


the result seems to be pretty predictable 
is there a way to improve RAND() or is there something better ?
Should I be using php to randomize the found set instead ?


many thanks
g


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Making ORDER BY RAND() more random

[Graham Anderson]

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.picture,
media.spanish as trackName,
media.path,
media.quality,
mediaType.id as mediaType
FROM artist, media, playlistItems, mediaType
WHERE playlistItems.playlist_id = $myID
AND playlistItems.media_id = media.id
AND media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
    ORDER BY RAND() LIMIT 0, 30";


the result seems to be pretty predictable 
is there a way to improve RAND() or is there something better ?



g


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY rand()

[Philippe Poelvoorde]

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 in just a "route" name appearing but no photo. 
How can I change the query to only pull up "routes" that have a 
"route_photo" listed in the DB?

Thanks,
CH

Maybe you should add something like this in your WHERE clause :
AND route_photo IS NOT NULL
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ORDER BY rand()

[Craig Hoffman]

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" name appearing but no photo. 
How can I change the query to only pull up "routes" that have a 
"route_photo" listed in the DB?

Thanks,
CH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Repost: Order by RAND + join problem

[Gerske, Sebastian]

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
---
| id | content   |
| 1  | apple  |
---

relation_table

| id |  id_table1 |  id_table2 |
| 1  |  1 |  1 |
| 2  |  1 |  2 |


table2
-
| id | content |
| 1  | bear |
| 2  | ape  |
-

The result should be somehting like:

Query1 (randomly generated):
--
| table1.id | table1.content | table2.id | table2.content |
| 1| apple  | 1| bear   |
-
or
Query2 (randomly generated):
--
| table1.id | table1.content | table2.id | table2.content |
| 1| apple  | 2| ape |
-

and so on ..

thanks for comments / suggestions / solutions



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: group by & order by rand() problem

[Alessandro Astarita]

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(),4),title)),7) AS Title
>FROM banners
>GROUP BY user_id;

Thank you so much. This solution works correctly. 

-- 
Alessandro 'Asterix' Astarita <[EMAIL PROTECTED]>
CapriOnLine S.r.l. http://www.caprionline.com/
"Unix IS user friendly. It's just selective about who its friend are"

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: group by & order by rand() problem

[Michael Stassen]

Right.  You're grouping by user_id and throwing in title, and you're hoping 
to influence which of the titles is chosen to go with user_id, but as title 
is neither part of your group nor part of an aggregate function, its value 
is undefined.  See the manual for an explanation 
<http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html>.

You could probably accomplish this with a variant of the MAX-CONCAT trick 
<http://www.mysql.com/doc/en/example-Maximum-column-group-row.html>. 
Something like:

  SELECT user_id,
 SUBSTRING(MAX(CONCAT(TRUNCATE(RAND(),4),title)),7) AS Title
  FROM banners
  GROUP BY user_id;
Michael

m.pheasant wrote:

Order by is working after the group stage. 
You would need an aggregate function which chooses a random row. 
Some other SQL implementations would not let you select a column that is not
also grouped (eg title) or in an aggregate function as in your select ...
group by ... example.

m


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
I have this table:

mysql> select * from banners;
++-+---+
| id | user_id | title |
++-+---+
|  1 |   1 | first banner  |
|  2 |   1 | second banner |
|  3 |   2 | third banner  |
|  4 |   2 | forth banner  |
|  5 |   2 | fifth banner  |
++-+---+
I would like to show a random banner for each user,
something like this:
first call
++-+---+
| id | user_id | title |
++-+---+
|  1 |   1 | first banner  |
|  3 |   2 | third banner  |
++-+---+
second call
++-+---+
| id | user_id | title |
++-+---+
|  2 |   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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: group by & order by rand() problem

[m.pheasant]

Order by is working after the group stage. 
You would need an aggregate function which chooses a random row. 
Some other SQL implementations would not let you select a column that is not
also grouped (eg title) or in an aggregate function as in your select ...
group by ... example.

m


-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] [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:
> 
> mysql> select * from banners;
> ++-+---+
> | id | user_id | title |
> ++-+---+
> |  1 |   1 | first banner  |
> |  2 |   1 | second banner |
> |  3 |   2 | third banner  |
> |  4 |   2 | forth banner  |
> |  5 |   2 | fifth banner  |
> ++-+---+
> 
> I would like to show a random banner for each user,
> something like this:
> 
> first call
> ++-+---+
> | id | user_id | title |
> ++-+---+
> |  1 |   1 | first banner  |
> |  3 |   2 | third banner  |
> ++-+---+
> 
> second call
> ++-+---+
> | id | user_id | title |
> ++-+---+
> |  2 |   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 General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: group by & order by rand() problem

[[EMAIL PROTECTED]]

> 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 |   1 | first banner  |
|  3 |   2 | third banner  |
++-+---+

second call
++-+---+
| id | user_id | title |
++-+---+
|  3 |   2 | third banner  |
|  1 |   1 | first banner  |
++-+---+

etc...

Alex

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: group by & order by rand() problem

[Dathan Vance Pattishall]

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:
> 
> mysql> select * from banners;
> ++-+---+
> | id | user_id | title |
> ++-+---+
> |  1 |   1 | first banner  |
> |  2 |   1 | second banner |
> |  3 |   2 | third banner  |
> |  4 |   2 | forth banner  |
> |  5 |   2 | fifth banner  |
> ++-+---+
> 
> I would like to show a random banner for each user,
> something like this:
> 
> first call
> ++-+---+
> | id | user_id | title |
> ++-+---+
> |  1 |   1 | first banner  |
> |  3 |   2 | third banner  |
> ++-+---+
> 
> second call
> ++-+---+
> | id | user_id | title |
> ++-+---+
> |  2 |   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 General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

group by & order by rand() problem

[[EMAIL PROTECTED]]

I have this table:

mysql> select * from banners;
++-+---+
| id | user_id | title |
++-+---+
|  1 |   1 | first banner  |
|  2 |   1 | second banner |
|  3 |   2 | third banner  |
|  4 |   2 | forth banner  |
|  5 |   2 | fifth banner  |
++-+---+

I would like to show a random banner for each user,
something like this:

first call
++-+---+
| id | user_id | title |
++-+---+
|  1 |   1 | first banner  |
|  3 |   2 | third banner  |
++-+---+

second call
++-+---+
| id | user_id | title |
++-+---+
|  2 |   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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY RAND() performance

[Sasha Pachev]

Donny Simonton wrote:
Neil,
We never delete from primary tables.  No questions asked!  We would just
mark a entry as deleted, and not select from it.
Another option you can do to solve your deletion problem is, select 35 rows
for example, when you really only 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.  So every 5 minutes
you have new random items.
Another way is to guess a reasonably narrow fixed-width random range for a 
column for which you have a key, and do ORDER BY RAND() LIMIT 1 inside it. If 
you guess it too narrow, double it and try again until you get enough records.

The key range estimation technique is also useful in a number of other 
situations, eg. when paging through search results.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
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

[Neil Gunton]

Donny Simonton wrote:
> 
> Neil,
> We never delete from primary tables.  No questions asked!  We would just
> mark a entry as deleted, and not select from it.
> 
> Another option you can do to solve your deletion problem is, select 35 rows
> for example, when you really only 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.  So every 5 minutes
> you have new random items.

Thanks again - lots of great suggestions. I use a reverse proxy caching
front end Web server which allows me to reduce load on the back-end
MySQL/mod_perl Apache processes. Thus by setting the expiration time of
the web pages appropriately I can reduce the number of times the random
pics page is executed, which kinda/sorta does what your last suggestion
suggests, I think.

I am all in favor of simplifying application code wherever possible, and
I would still love to just use a simple query with "ORDER BY RAND()", it
would make things SOOO much more straightforward. So if any of the core
MySQL developers are reading this, please take a look at the original
question and let me know if there are any plans in the works to make
this more efficient (or if it's even possible - if it's just inherently
difficult then that would be good to know).

Thanks,

-Neil

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

[Donny Simonton]

Neil,
We never delete from primary tables.  No questions asked!  We would just
mark a entry as deleted, and not select from it.

Another option you can do to solve your deletion problem is, select 35 rows
for example, when you really only 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.  So every 5 minutes
you have new random items.

Donny

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
> Neil Gunton
> Sent: Monday, March 08, 2004 3:11 PM
> To: Donny Simonton
> Cc: [EMAIL PROTECTED]; 'MySQL'
> Subject: Re: ORDER BY RAND() performance
>
> Donny Simonton wrote:
> > One other option that we use
> > sometimes is say you need 30 results randomized, and you have an
> > auto-increment in your table.  Create 30 random numbers, then do a
> select
> > with something like this:
> >
> > Select * from blabla where lkajsdlkjas IN (10, 43, 22, 8981, etc...)
> >
> > This works fairly well, but then again, I haven't benchmarked it in a
> while
> > and don't really remember how well it works.  Actually, I just tried
> this on
> > a table with 43 million entries and it took 0.0004 seconds.
>
> I was thinking about something similar, but how do you handle cases
> where you might have gaps in the auto-increment sequence? For example,
> if you delete record 100, and then one of the random numbers you
> generate happens to be 100, you will be short 1 record because it
> doesn't exist. If records never get deleted from the table then there's
> no issue, but in my application it does happen, so gaps will occur. I
> have looked around for an easy way to maintain a table with a key that
> acts like a "position" marker, but it doesn't seem to be out there. In
> other words, if you had a table with n records, then each record would
> have a field which has a value corresponding to the record's position in
> the table, from 1 to n. This "position" can be simply the order the
> records were inserted or the order that they exist on the disk - it
> doesn't really matter, since this position field would 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
> available then "ORDER BY RAND()" optimization would be easy, since you
> could have the "sequence" field be a primary key and then just do
> "select where sequence in (...)", and it would be very fast. This could
> be done internally for "ORDER BY RAND()", or you could do the select
> yourself, using a better random number generator if you so wish.
>
> Thanks for the suggestions,
>
> -Neil
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]




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

[Neil Gunton]

Donny Simonton wrote:
> One other option that we use
> sometimes is say you need 30 results randomized, and you have an
> auto-increment in your table.  Create 30 random numbers, then do a select
> with something like this:
> 
> Select * from blabla where lkajsdlkjas IN (10, 43, 22, 8981, etc...)
> 
> This works fairly well, but then again, I haven't benchmarked it in a while
> and don't really remember how well it works.  Actually, I just tried this on
> a table with 43 million entries and it took 0.0004 seconds.

I was thinking about something similar, but how do you handle cases
where you might have gaps in the auto-increment sequence? For example,
if you delete record 100, and then one of the random numbers you
generate happens to be 100, you will be short 1 record because it
doesn't exist. If records never get deleted from the table then there's
no issue, but in my application it does happen, so gaps will occur. I
have looked around for an easy way to maintain a table with a key that
acts like a "position" marker, but it doesn't seem to be out there. In
other words, if you had a table with n records, then each record would
have a field which has a value corresponding to the record's position in
the table, from 1 to n. This "position" can be simply the order the
records were inserted or the order that they exist on the disk - it
doesn't really matter, since this position field would 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
available then "ORDER BY RAND()" optimization would be easy, since you
could have the "sequence" field be a primary key and then just do
"select where sequence in (...)", and it would be very fast. This could
be done internally for "ORDER BY RAND()", or you could do the select
yourself, using a better random number generator if you so wish.

Thanks for the suggestions,

-Neil

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

[Neil Gunton]

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 1 x 30)

Thanks! I did see this suggested on another forum. However when I tried
it, I found that EXPLAIN wasn't very encouraging. Using this minimal
table:

CREATE TABLE visible_pics (
  pic_id int(10) unsigned NOT NULL default '0',
  doc_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (pic_id),
  KEY doc_id (doc_id)
) TYPE=MyISAM;

mysql> explain select * from visible_pics limit 1,1;
+--+--+---+--+-+--+---+---+
| table| type | possible_keys | key  | key_len | ref  | rows  |
Extra |
+--+--+---+--+-+--+---+---+
| visible_pics | ALL  | NULL  | NULL |NULL | NULL | 19633
|   |
+--+--+---+--+-+--+---+---+
1 row in set (0.00 sec)

mysql> explain select * from visible_pics order by pic_id limit 1,1;
+--+---+---+-+-+--+---+---+
| table| type  | possible_keys | key | key_len | ref  |
rows  | Extra |
+--+---+---+-+-+--+---+---+
| visible_pics | index | NULL  | PRIMARY |   4 | NULL |
19633 |   |
+--+---+---+-+-+--+---+---+
1 row in set (0.00 sec)

In both cases, the number of rows which will be scanned is close to the
total number of rows. I included the second EXPLAIN to see if using
pic_id (the primary key) would make any difference. It actually seems to
actually be faster without using the index, in my trivial tests:

mysql> select * from visible_pics order by pic_id limit 1,1;
+++
| pic_id | doc_id |
+++
|  11669 |258 |
+++
1 row in set (0.09 sec)

mysql> select * from visible_pics order by pic_id limit 10100,1;
+++
| pic_id | doc_id |
+++
|  11771 |258 |
+++
1 row in set (0.08 sec)

mysql> select * from visible_pics limit 10100,1;
+++
| pic_id | doc_id |
+++
|  11750 |258 |
+++
1 row in set (0.02 sec)

mysql> select * from visible_pics limit 12100,1;
+++
| pic_id | doc_id |
+++
|  14085 |269 |
+++
1 row in set (0.02 sec)

mysql> select * from visible_pics limit 900,1;
+++
| pic_id | doc_id |
+++
|   1100 | 53 |
+++
1 row in set (0.01 sec)

mysql> select * from visible_pics limit 18000,1;
+++
| pic_id | doc_id |
+++
|  20343 |387 |
+++
1 row in set (0.03 sec)

mysql> 
mysql> select * from visible_pics order by pic_id limit 12000,1;
+++
| pic_id | doc_id |
+++
|  13857 |325 |
+++
1 row in set (0.10 sec)

The last one was just to confirm that there wasn't some kind of disk
caching going on that affected the results. The query without using the
index was definitely faster. If the average query is about 0.05 second,
and you do 30 of them, then that would give about 1.5 seconds for the
whole thing. This is in fact worse than just doing the "ORDER BY RAND()
LIMIT 30" on the same table:

mysql> select * from visible_pics order by rand() limit 30;
+++
| pic_id | doc_id |
+++
|   4149 | 98 |
|   5030 |148 |
|   1911 | 69 |
|   4258 |105 |
|  14131 |170 |
|  17047 |165 |
|  12643 |319 |
|  14271 |180 |
|   1815 | 69 |
|  12768 |260 |
|   8118 |164 |
|   2339 | 87 |
|   3058 | 63 |
|   2573 | 46 |
|  11511 |230 |
|  16939 |335 |
|   7749 |113 |
|   6921 |164 |
|   2106 | 79 |
|   3609 | 91 |
|  12513 |259 |
|  18169 |234 |
|  19173 |372 |
|  11912 |305 |
|   2026 | 69 |
|   7697 |222 |
|  20834 |447 |
|977 | 53 |
|   1638 | 24 |
|  13986 |308 |
+++
30 rows in set (0.22 sec)

This isn't as simple as it appears at first, however - this is merely
the query to get 30 random pic_id's. I then have to do 30 more queries
to get the "real" records in the separate table, so that I can build the
HTML page with filenames, captions etc.

Thanks again - this is a good one to know about (for anyone else out
there who is encountering the same issues). But the above tests were on
a very minimal table, with no "where" clause, because the table was
specially prepared to only contain the relevant records in the first
place.

I am stil

RE: ORDER BY RAND() performance

[Donny Simonton]

Exactly, it's faster for us to pull all of the data and then randomize it
locally.  We have benchmarked it both ways and the local randomize was
faster.  Now that's if you want multiple records returned.

Now if you only want one record, what we do, is create a random number, and
then 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 30 results randomized, and you have an
auto-increment in your table.  Create 30 random numbers, then do a select
with something like this:

Select * from blabla where lkajsdlkjas IN (10, 43, 22, 8981, etc...)

This works fairly well, but then again, I haven't benchmarked it in a while
and don't really remember how well it works.  Actually, I just tried this on
a table with 43 million entries and it took 0.0004 seconds.

Just some ideas.

Donny

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 08, 2004 2:36 PM
> To: Donny Simonton
> Cc: 'Neil Gunton'; 'MySQL'
> Subject: RE: ORDER BY RAND() performance
>
>
> Donny,  what do you do?  Throw all the values into an array or something
> on the client side, and use a random number generator to pull out the
> array elements?
>
> I suppose (depending on resultset size) pulling that many rows from server
> to client and handing on client side could be faster...
>
>
>
>
> On 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.  Splitting your data into a hundred tables will still make it
> "using
> > temporary, using filesort".
> >
> > I just did a little test, where I only had 5 entries in a table, and I
> using
> > temp using filesort.
> >
> > Will it ever be improved?  Probably the same time order by DESC is
> improved.
> >
> > Donny
> >
> >
> >
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > > Sent: Monday, March 08, 2004 2:05 PM
> > > To: Neil Gunton
> > > Cc: MySQL
> > > Subject: Re: ORDER BY RAND() performance
> > >
> > >
> > > If your infact (sounds like) storing the pictures meta-data (name,
> size,
> > > owner, etc) and the data (blob of some kind) .. I would definately
> break
> > > up the design into 2 tables.  That way when dealing with the meta-data
> > > table (your RAND() query) there is much less data that needs to be
> > > traversed to get your answer, which should result in a faster query.
> > >
> > >
> > >
> > >
> > > On Mon, 8 Mar 2004, Neil Gunton wrote:
> > >
> > > > 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 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 ORDER BY RAND(). The reason appears to be that when you do
> EXPLAIN
> > > > on a query using this, MySQL reports "Using temporary; Using
> filesort",
> > > > which is the worst possible result. Also, the number of rows
> reported is
> > > > pretty much the entire set. So, presumably, the current
> implementation
> > > > of ORDER BY RAND() means that MySQL has to traverse the entire
> table,
> > > > regardless of other indexes.
> > > >
> > > > There are, of course, other ways to get around this, but they are
> all
> > > > more complex than simply using ORDER BY RAND(). I think that
> selecting a
> > > > random number of records from a table is something that a lot of
> > > > websites would like to be able to do, and so as datasets get larger
> it
> > > > would be nice to see this function scale well. For anyone who has a
> > > > website with a large archive of data, the ability to present a
&

Re: ORDER BY RAND() performance

[Ray]

On Monday 08 March 2004 14:14, Neil Gunton wrote:
> [EMAIL PROTECTED] wrote:
> > If your infact (sounds like) storing the pictures meta-data
> > (name, size, owner, etc) and the data (blob of some kind) .. I
> > would definately break up the design into 2 tables.  That way
> > when dealing with the meta-data table (your RAND() query) there
> > is much less data that needs to be traversed to get your answer,
> > which should result in a faster query.
>
> Thanks! This is definitely good advice, but unfortunately it
> doesn't solve the RAND() 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 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 reduced it to pretty much the minimal case,
> given that I want a random selection of ALL the records.
>
> I don't know the internals of how MySQL could optimize this sort of
> thing, but I was thinking that perhaps there was some kind of
> internal trickery it could do to select random record positions and
> then get those very quickly, without having to traverse the entire
> table. I think if the table has no varchar fields then it should be
> easy (at least in MyISAM) to calculate the record position based on
> the record number. So I think it *should* in theory be possible to
> optimize this, but I just don't know if anyone has realized that
> it's an issue, or if they are planning on doing anything about it.
> Any insights from MySQL internal developers? Or should I be posting
> this to the "internals" list?
>
> Thanks again,
>
> -Neil

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 1 x 30)

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

[colbey]

Donny,  what do you do?  Throw all the values into an array or something
on the client side, and use a random number generator to pull out the
array elements?

I suppose (depending on resultset size) pulling that many rows from server
to client and handing on client side could be faster...




On 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.  Splitting your data into a hundred tables will still make it "using
> temporary, using filesort".
>
> I just did a little test, where I only had 5 entries in a table, and I using
> temp using filesort.
>
> Will it ever be improved?  Probably the same time order by DESC is improved.
>
> Donny
>
>
>
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: Monday, March 08, 2004 2:05 PM
> > To: Neil Gunton
> > Cc: MySQL
> > Subject: Re: ORDER BY RAND() performance
> >
> >
> > If your infact (sounds like) storing the pictures meta-data (name, size,
> > owner, etc) and the data (blob of some kind) .. I would definately break
> > up the design into 2 tables.  That way when dealing with the meta-data
> > table (your RAND() query) there is much less data that needs to be
> > traversed to get your answer, which should result in a faster query.
> >
> >
> >
> >
> > On Mon, 8 Mar 2004, Neil Gunton wrote:
> >
> > > 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 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 ORDER BY RAND(). The reason appears to be that when you do EXPLAIN
> > > on a query using this, MySQL reports "Using temporary; Using filesort",
> > > which is the worst possible result. Also, the number of rows reported is
> > > pretty much the entire set. So, presumably, the current implementation
> > > of ORDER BY RAND() means that MySQL has to traverse the entire table,
> > > regardless of other indexes.
> > >
> > > There are, of course, other ways to get around this, but they are all
> > > more complex than simply using ORDER BY RAND(). I think that selecting a
> > > random number of records from a table is something that a lot of
> > > websites would like to be able to do, and so as datasets get larger it
> > > would be nice to see this function scale well. For anyone who has a
> > > website with a large archive of data, the ability to present a random
> > > selection of this data is very useful.
> > >
> > > I would like to know if anyone knows if the MySQL team is aware of this
> > > problem, and if so whether they are planning on improving it at any
> > > point. I ask mainly because if I am told that "yes, it'll be much better
> > > in version X" then I can live with the couple of seconds that it takes
> > > currently, knowing that this will be better down the line. However if I
> > > am advised that this is a fundamentally hard problem for whatever
> > > reason, then I will put the effort into reworking my tables to use an
> > > alternative solution.
> > >
> > > The only real solution that I can see which is fast is to make another
> > > table which contains just the unique IDs of the pictures that are
> > > visible (there are others which are not publicly visible, and which
> > > shouldn't be included in the random query, so making a separate table
> > > with the appropriate subset makes sense for performance). This new table
> > > will have a primary key which is a numeric "sequence" field. Every
> > > record will have its own sequence number, going from 1 up to the number
> > > of records. Then, instead of doing one query with ORDER BY RAND() LIMIT
> > > 30, I can instead do 30 queries, each with a different random sequence
> > > (generated from Perl), which will look up the unique sequence number.
> > > Since this is a primary key, it will be very fast, so that doing 30
> > > queries will not have a big perfo

RE: ORDER BY RAND() performance

[Donny Simonton]

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 "using
temporary, using filesort".

I just did a little test, where I only had 5 entries in a table, and I using
temp using filesort.

Will it ever be improved?  Probably the same time order by DESC is improved.

Donny



> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 08, 2004 2:05 PM
> To: Neil Gunton
> Cc: MySQL
> Subject: Re: ORDER BY RAND() performance
>
>
> If your infact (sounds like) storing the pictures meta-data (name, size,
> owner, etc) and the data (blob of some kind) .. I would definately break
> up the design into 2 tables.  That way when dealing with the meta-data
> table (your RAND() query) there is much less data that needs to be
> traversed to get your answer, which should result in a faster query.
>
>
>
>
> On Mon, 8 Mar 2004, Neil Gunton wrote:
>
> > 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 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 ORDER BY RAND(). The reason appears to be that when you do EXPLAIN
> > on a query using this, MySQL reports "Using temporary; Using filesort",
> > which is the worst possible result. Also, the number of rows reported is
> > pretty much the entire set. So, presumably, the current implementation
> > of ORDER BY RAND() means that MySQL has to traverse the entire table,
> > regardless of other indexes.
> >
> > There are, of course, other ways to get around this, but they are all
> > more complex than simply using ORDER BY RAND(). I think that selecting a
> > random number of records from a table is something that a lot of
> > websites would like to be able to do, and so as datasets get larger it
> > would be nice to see this function scale well. For anyone who has a
> > website with a large archive of data, the ability to present a random
> > selection of this data is very useful.
> >
> > I would like to know if anyone knows if the MySQL team is aware of this
> > problem, and if so whether they are planning on improving it at any
> > point. I ask mainly because if I am told that "yes, it'll be much better
> > in version X" then I can live with the couple of seconds that it takes
> > currently, knowing that this will be better down the line. However if I
> > am advised that this is a fundamentally hard problem for whatever
> > reason, then I will put the effort into reworking my tables to use an
> > alternative solution.
> >
> > The only real solution that I can see which is fast is to make another
> > table which contains just the unique IDs of the pictures that are
> > visible (there are others which are not publicly visible, and which
> > shouldn't be included in the random query, so making a separate table
> > with the appropriate subset makes sense for performance). This new table
> > will have a primary key which is a numeric "sequence" field. Every
> > record will have its own sequence number, going from 1 up to the number
> > of records. Then, instead of doing one query with ORDER BY RAND() LIMIT
> > 30, I can instead do 30 queries, each with a different random sequence
> > (generated from Perl), which will look up the unique sequence number.
> > Since this is a primary key, it will be very fast, so that doing 30
> > queries will not have a big performance impact. However this scheme
> > requires that the sequences in the new table be kept very consistent -
> > for example, if a picture is removed from the sequence then the sequence
> > numbers above that record have to be updated. This introduces potential
> > for error, but it is a possible solution. I don't want to implement it,
> > obviously, if ORDER BY RAND() is slated for improvement.
> >
> > Thanks for any ideas or insights...
> >
> > -Neil Gunton
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]




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

[Neil Gunton]

[EMAIL PROTECTED] wrote:
> 
> If your infact (sounds like) storing the pictures meta-data (name, size,
> owner, etc) and the data (blob of some kind) .. I would definately break
> up the design into 2 tables.  That way when dealing with the meta-data
> table (your RAND() query) there is much less data that needs to be
> traversed to get your answer, which should result in a faster query.

Thanks! This is definitely good advice, but unfortunately it doesn't
solve the RAND() 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 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 reduced it to pretty much the minimal case, given that I want a
random selection of ALL the records.

I don't know the internals of how MySQL could optimize this sort of
thing, but I was thinking that perhaps there was some kind of internal
trickery it could do to select random record positions and then get
those very quickly, without having to traverse the entire table. I think
if the table has no varchar fields then it should be easy (at least in
MyISAM) to calculate the record position based on the record number. So
I think it *should* in theory be possible to optimize this, but I just
don't know if anyone has realized that it's an issue, or if they are
planning on doing anything about it. Any insights from MySQL internal
developers? Or should I be posting this to the "internals" list?

Thanks again,

-Neil

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

[colbey]

If your infact (sounds like) storing the pictures meta-data (name, size,
owner, etc) and the data (blob of some kind) .. I would definately break
up the design into 2 tables.  That way when dealing with the meta-data
table (your RAND() query) there is much less data that needs to be
traversed to get your answer, which should result in a faster query.




On Mon, 8 Mar 2004, Neil Gunton wrote:

> 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 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 ORDER BY RAND(). The reason appears to be that when you do EXPLAIN
> on a query using this, MySQL reports "Using temporary; Using filesort",
> which is the worst possible result. Also, the number of rows reported is
> pretty much the entire set. So, presumably, the current implementation
> of ORDER BY RAND() means that MySQL has to traverse the entire table,
> regardless of other indexes.
>
> There are, of course, other ways to get around this, but they are all
> more complex than simply using ORDER BY RAND(). I think that selecting a
> random number of records from a table is something that a lot of
> websites would like to be able to do, and so as datasets get larger it
> would be nice to see this function scale well. For anyone who has a
> website with a large archive of data, the ability to present a random
> selection of this data is very useful.
>
> I would like to know if anyone knows if the MySQL team is aware of this
> problem, and if so whether they are planning on improving it at any
> point. I ask mainly because if I am told that "yes, it'll be much better
> in version X" then I can live with the couple of seconds that it takes
> currently, knowing that this will be better down the line. However if I
> am advised that this is a fundamentally hard problem for whatever
> reason, then I will put the effort into reworking my tables to use an
> alternative solution.
>
> The only real solution that I can see which is fast is to make another
> table which contains just the unique IDs of the pictures that are
> visible (there are others which are not publicly visible, and which
> shouldn't be included in the random query, so making a separate table
> with the appropriate subset makes sense for performance). This new table
> will have a primary key which is a numeric "sequence" field. Every
> record will have its own sequence number, going from 1 up to the number
> of records. Then, instead of doing one query with ORDER BY RAND() LIMIT
> 30, I can instead do 30 queries, each with a different random sequence
> (generated from Perl), which will look up the unique sequence number.
> Since this is a primary key, it will be very fast, so that doing 30
> queries will not have a big performance impact. However this scheme
> requires that the sequences in the new table be kept very consistent -
> for example, if a picture is removed from the sequence then the sequence
> numbers above that record have to be updated. This introduces potential
> for error, but it is a possible solution. I don't want to implement it,
> obviously, if ORDER BY RAND() is slated for improvement.
>
> Thanks for any ideas or insights...
>
> -Neil Gunton
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ORDER BY RAND() performance

[Neil Gunton]

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 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 ORDER BY RAND(). The reason appears to be that when you do EXPLAIN
on a query using this, MySQL reports "Using temporary; Using filesort",
which is the worst possible result. Also, the number of rows reported is
pretty much the entire set. So, presumably, the current implementation
of ORDER BY RAND() means that MySQL has to traverse the entire table,
regardless of other indexes.

There are, of course, other ways to get around this, but they are all
more complex than simply using ORDER BY RAND(). I think that selecting a
random number of records from a table is something that a lot of
websites would like to be able to do, and so as datasets get larger it
would be nice to see this function scale well. For anyone who has a
website with a large archive of data, the ability to present a random
selection of this data is very useful.

I would like to know if anyone knows if the MySQL team is aware of this
problem, and if so whether they are planning on improving it at any
point. I ask mainly because if I am told that "yes, it'll be much better
in version X" then I can live with the couple of seconds that it takes
currently, knowing that this will be better down the line. However if I
am advised that this is a fundamentally hard problem for whatever
reason, then I will put the effort into reworking my tables to use an
alternative solution.

The only real solution that I can see which is fast is to make another
table which contains just the unique IDs of the pictures that are
visible (there are others which are not publicly visible, and which
shouldn't be included in the random query, so making a separate table
with the appropriate subset makes sense for performance). This new table
will have a primary key which is a numeric "sequence" field. Every
record will have its own sequence number, going from 1 up to the number
of records. Then, instead of doing one query with ORDER BY RAND() LIMIT
30, I can instead do 30 queries, each with a different random sequence
(generated from Perl), which will look up the unique sequence number.
Since this is a primary key, it will be very fast, so that doing 30
queries will not have a big performance impact. However this scheme
requires that the sequences in the new table be kept very consistent -
for example, if a picture is removed from the sequence then the sequence
numbers above that record have to be updated. This introduces potential
for error, but it is a possible solution. I don't want to implement it,
obviously, if ORDER BY RAND() is slated for improvement.

Thanks for any ideas or insights...

-Neil Gunton

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

order by rand()

[H Marc Bower]

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-party server over which I have little control, but the params are as 
follows:  php 4.0.3pl1, mysql 3.22.32.  I've read of problems with this on Win32 
systems, but this runs on linux using apache.  Any assistance would be appreciated.

Thank you,

Marc

-=-=-=-=-
No testimony is sufficient to establish a miracle unless the testimony be of such a 
kind that its falsehood would be more miraculous than the fact which it endeavours to 
establish. - David Hume
-=-=-=-=-

Re: ORDER BY RAND() not working

[Arthur Fuller]

I don't have your exact setup installed so cannot verify this, but how about
rewriting your statement a little? I just tried this in mysql and it works
fine, so it may from php. Let me know :-)

Change the query to:

SELECT *, Rand() as MyOrder
FROM products
WHERE sale_price IS NOT NULL
ORDER 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 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 error message is as follows:
>
> You have an error in your SQL syntax near 'RAND() LIMIT 3' at line 1
>
> Jamie
>
> Jamie Tibbetts.[EMAIL PROTECTED]
> Epigroove .http://www.epigroove.com/
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: ORDER BY RAND() not working

[Jay Blanchard]

[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 = "select * from tblBAR WHERE DiskSize IS NOT NULL ORDER BY RAND()
LIMIT 3 ";
if(!($db = mysql_query($query, $dbconnect))){
print("MySQL reports: " . mysql_error() . "\n");
exit();
}
while($row = mysql_fetch_object($db)){
print($row->ID . " " . $row->ClientName . "");
}

This worked fine for me. Maybe there is not a space between NULL and ORDER?

Jay



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: ORDER BY RAND() not working

[Jamie Tibbetts]

> $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 error message is as follows:

You have an error in your SQL syntax near 'RAND() LIMIT 3' at line 1

Jamie

Jamie Tibbetts.[EMAIL PROTECTED]
Epigroove .http://www.epigroove.com/


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: ORDER BY RAND() not working

[Alain Fontaine]

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 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 from the query, it
works in PHP.

Doesn't work:
SELECT * FROM products WHERE sale_price IS NOT NULL ORDER BY RAND() LIMIT 3

Works:
SELECT * FROM products WHERE sale_price IS NOT NULL LIMIT 3

Does anyone have any idea why this is happening?

Thanks,
Jamie

Jamie Tibbetts.[EMAIL PROTECTED]
Epigroove .http://www.epigroove.com/


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: ORDER BY RAND() not working

[Jay Blanchard]

[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() part from the query, it
works in PHP.

Doesn't work:
SELECT * FROM products WHERE sale_price IS NOT NULL ORDER BY RAND() LIMIT 3

Works:
SELECT * FROM products WHERE sale_price IS NOT NULL LIMIT 3

Does anyone have any idea why this is happening?
[/snip]

Need to see your PHP code, that's where it's broken.

Jay
sql, mysql, query :^)



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

ORDER BY RAND() not working

[Jamie Tibbetts]

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 from the query, it
works in PHP.

Doesn't work:
SELECT * FROM products WHERE sale_price IS NOT NULL ORDER BY RAND() LIMIT 3

Works:
SELECT * FROM products WHERE sale_price IS NOT NULL LIMIT 3

Does anyone have any idea why this is happening?

Thanks,
Jamie

Jamie Tibbetts.[EMAIL PROTECTED]
Epigroove .http://www.epigroove.com/


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Performance of ORDER BY RAND()

[Scott Gifford]

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 random number between the first and last row
and display that row.

Is there any way to speed this up?

For the common case above, I've been looking at assigning each row a
number, then just counting rows, picking a number between one and the
number of rows, and selecting that records by number; that seemed to
be about 25 times faster in quick benchmarks.  Unfortunately, it
doesn't work well if I need to use any WHERE clauses in my select,
since I don't know what random numbers to pick.

Has anybody done something like this before who would like to share
some tips?

Thanks!

ScottG.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: order by rand() question

[Chris Petersen]

>> 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 SELECT * FROM theTable ORDER BY RAND() LIMIT 5; would
> do, since RAND() returns a different value for each row, and the date column
> would only be sorted in the event of duplicates in the previous order
> expression.

Gotcha.  So is there any way to return 5 (some number) rows, chosen
randomly, and then sort them by date (or name or whatever).  So the final
result is a list, sorted by date, but of rows chosen randomly from the
table.

I assume this would be possible with some kind of nested SELECT statement,
but last I checked, mysql couldn't do this.

Thanks,

Chris


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: order by rand() question

[Chris Bolt]

> Gotcha.  So is there any way to return 5 (some number) rows, chosen
> randomly, and then sort them by date (or name or whatever).  So the final
> result is a list, sorted by date, but of rows chosen randomly from the
> table.

CREATE TEMPORARY TABLE temptable TYPE=HEAP SELECT * FROM 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 archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: order by rand() question

[Chris Bolt]

> 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 SELECT * FROM theTable ORDER BY RAND() LIMIT 5; would
do, since RAND() returns a different value for each row, and the date column
would only be sorted in the event of duplicates in the previous order
expression.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: order by rand() question

[Chris Petersen]

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 number of rows, randomly, and then
sort them by date (or another field).  Or is there a way to sort the rows
AFTER they are put through LIMIT? (sort of like how HAVING is to WHERE)

Thanks,

Chris


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: order by rand() question

[Chris Bolt]

> 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 BY RAND() LIMIT 5;
(clipped)
> 5 rows in set (0.08 sec)
>
> Right, that's *less than a tenth of a second*, and that's on a crufy old
> P200 that also happens to be serving our company Intranet at the same time
> as doing this little experiment.
>
> So what's so inefficient? I can't blink in 0.08 seconds, let alone think a
> query is running too slowly.

Watch out though. Sure, it may be that fast when you're selecting one row
out of 10,000 ints on a lightly loaded server. However MySQL works this way:

- SELECT * FROM mytable;
- Order all found rows by rand()
- Send back the first 5

It's not that inefficient when you just have one column, since that's all
you'll be getting anyways, however I have a table with 38 columns, 141000
rows and lots of data you are making MySQL get a lot of data it doesn't end
up using. This is how fast your fast query runs on it (granted this is on a
server with medium to heavy load and ~50 queries per second):

mysql> select * from users order by rand() limit 1;
...
1 row in set (1 min 29.48 sec)

You can make this a little faster:

mysql> select id from users order by rand() limit 1;
...
1 row in set (10.10 sec)

mysql> select * from users where id = 123456;
...
1 row in set (0.12 sec)

But it's still quite inefficient. The best way would be to generate the
random numbers from your application. If you wanted five rows, generate 10
numbers (just to be really safe in case a few reference deleted rows)
between 1 and max(id) (assuming you have an auto_increment column named id)
and do:

mysql> select max(id) from users;
...
1 row in set (0.12 sec)

mysql> select * from users where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) limit
5;
...
5 rows in set (0.33 sec)


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: order by rand() question

[Gerald Clark]

You asked this earlier this week.
The answer has not changed.

Matt Heaton wrote:

> 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 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 slow and inefficient, but I am afraid that is how it works?  Is 
> there anyone out there that knows FOR SURE how the order by rand() operation 
> works?  I can't find a specification out there for it.
> 
> Thanks,
> Matt Heaton
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
><[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: order by rand() question

[Jon Haworth]

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() LIMIT 5;
+-+
| myfield |
+-+
|9935 |
|3221 |
|1530 |
|3889 |
|2133 |
+-+
5 rows in set (0.08 sec)

Right, that's *less than a tenth of a second*, and that's on a crufy old
P200 that also happens to be serving our company Intranet at the same time
as doing this little experiment.

So what's so inefficient? I can't blink in 0.08 seconds, let alone think a
query is running too slowly.

Cheers
Jon


-Original Message-
From: Matt Heaton [mailto:[EMAIL PROTECTED]]
Sent: 15 June 2001 14:23
To: [EMAIL PROTECTED]
Subject: 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 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 slow and inefficient, but I am afraid that is how it works?  Is 
there anyone out there that knows FOR SURE how the order by rand() operation

works?  I can't find a specification out there for it.

Thanks,
Matt Heaton

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



**
'The information included in this Email is of a confidential nature and is 
intended only for the addressee. If you are not the intended addressee, 
any disclosure, copying or distribution by you is prohibited and may be 
unlawful. Disclosure to any party other than the addressee, whether 
inadvertent or otherwise is not intended to waive privilege or
confidentiality'

**

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

order by rand() question

[Matt Heaton]

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 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 slow and inefficient, but I am afraid that is how it works?  Is 
there anyone out there that knows FOR SURE how the order by rand() operation 
works?  I can't find a specification out there for it.

Thanks,
Matt Heaton

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Question about order by rand()

[Peter van Dijk]

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 where number=1 order by rand() limit 1;
> 
> 
> What if 100 rows match?  Does it first get all 100 rows and then randomly
> choose one?

Ofcourse.

> Or does it find the first one and stop?  If it finds 100 rows and then
> chooses one this
> is VERY inefficient.  If it finds one and then stops, what determines which
> row it starts
> searching on?

If it stops at the first one, it can't very well return a random one
from the set. It therefore has to find those 100 entries first.

> If anyone knows FOR SURE how this works please let me know... I need to be
> able to grab
> a single row FAST even if a bunch of rows match.  Let me know if you have
> any ideas.

Let's just say the query is executed from left to right quite nicely.
With indexing on 'number' this should be quite fast, still.

Greetz, Peter
-- 
Against Free Sex!   http://www.dataloss.nl/Megahard_en.html

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Question about order by rand()

[Matt Heaton]

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 then randomly
choose one?
Or does it find the first one and stop?  If it finds 100 rows and then
chooses one this
is VERY inefficient.  If it finds one and then stops, what determines which
row it starts
searching on?

If anyone knows FOR SURE how this works please let me know... I need to be
able to grab
a single row FAST even if a bunch of rows match.  Let me know if you have
any ideas.

Thanks,
Matt


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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 | Extra  | 
+---+--+--+-+-++ 
| num   | int(11)  |  | PRI | NULL| auto_increment | 
| id| varchar(20)  | YES  | MUL | NULL|| 
| name  | varchar(20)  | YES  | MUL | NULL|| 
| address   | varchar(50)  | YES  | | NULL|| 
| sex   | char(1)  | YES  | | NULL|| 
| point | int(11)  | YES  | | NULL|| 
| introduce | varchar(255) | YES  | | NULL|| 
+---+--+--+-+-++ 
7 rows in set (0.00 sec) 

mysql> select count(*) from member ; 
+--+ 
| count(*) | 
+--+ 
|  100 | 
+--+ 
1 row in set (0.00 sec) 

mysql> select id, name from member order by rand() limit 1 ; 
+--+--+ 
| id   | name | 
+--+--+ 
| CCDJUBUSRMUSKBDTMNPW | XMQPSKIMKFCWGPNDSGRD | 
+--+--+ 
1 row in set (2 min 42.14 sec) 

( I inserted column values randomly selected alphabet) 

mysql> select id, name from member limit 12313, 1; 
+--+--+ 
| id   | name | 
+--+--+ 
| FAICNUXHARYCCLVMIDDS | UYQEGOTCYPRRPZIYRDYK | 
+--+--+ 
1 row in set (1.35 sec) 

mysql> select version() ; 
+---+ 
| version() | 
+---+ 
| 3.23.32   | 
+---+ 
1 row in set (0.00 sec) 

[wertyu@inos ~]$ uname -a 
Linux  2.2.5-22 #1 Thu Nov 23 18:33:46 KST 2000 i686 unknown 

Hardware : Pentium II 233Mhz with 128Mb Memory 

My suggestion is if somebody uses order by rand(), it's better to change to sel
ect count() record, and generate random number between 1 and row count, and ret
rieve with LIMIT rand_num, 1 

Am I correct? 

Thank you~ 




--MIME Multi-part separator--


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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

[Sinisa Milivojevic]

[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 fine.  It just seems that adding the limit clause causes
 > something to break.
 > >How-To-Repeat:
 > Easy way:
 > mysql mysql
 > select * from users order by rand() limit 10
 > On my database machine using the latest stable binray release it fails.
 > >Fix:
 > None known
 > 
 > >Submitter-Id:   
 > >Originator: Shawn Wallace
 > >Organization:
 > Justweb Inc.
 > >MySQL support: [none | licence | email support | extended email support ]
 > >Synopsis:   Problem combining order by rand() with limit
 > >Severity:   serious
 > >Priority:   medium
 > >Category:   mysql
 > >Class:  sw-bug
 > >Release:mysql-3.23.35 (Official MySQL RPM)
 > >Server: /usr/bin/mysqladmin  Ver 8.18 Distrib 3.23.35, for pc-linux-gnu on i686
 > Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
 > This software comes with ABSOLUTELY NO WARRANTY. This is free software,
 > and you are welcome to modify and redistribute it under the GPL license
 > 
 > Server version   3.23.35
 > Protocol version 10
 > Connection   Localhost via UNIX socket
 > UNIX socket  /var/lib/mysql/mysql.sock
 > Uptime:  5 hours 56 min 1 sec
 > 
 > Threads: 3  Questions: 160022  Slow queries: 0  Opens: 106  Flush tables: 1  Open 
 >tables: 100 Queries per second avg: 7.491
 > >Environment:
 >  
 > System: Linux darthvader 2.2.18pre21-RAID #2 Wed Feb 14 17:21:03 EST 2001 i686 
 >unknown
 > Architecture: i686
 > 
 > Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
 > GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.2/specs
 > gcc version 2.95.2 2220 (Debian GNU/Linux)
 > Compilation info: CC='egcs'  CFLAGS='-O6 -fomit-frame-pointer -mpentium'  CXX='egcs' 
 > CXXFLAGS='-O6 -fomit-frame-pointer   -felide-constructors 
 >-fno-exceptions -fno-rtti -mpentium'  LDFLAGS=''
 > LIBC: 
 > lrwxrwxrwx1 root root   13 Mar 12 13:22 /lib/libc.so.6 -> 
 >libc-2.1.3.so
 > -rwxr-xr-x1 root root   887712 Jan 15 15:06 /lib/libc-2.1.3.so
 > -rw-r--r--1 root root  2089496 Jan 15 15:07 /usr/lib/libc.a
 > -rw-r--r--1 root root  178 Jan 15 15:07 /usr/lib/libc.so
 > Configure command: ./configure  --disable-shared --with-mysqld-ldflags=-all-static 
 >--with-client-ldflags=-all-static --enable-assembler --with-mysqld-user=mysql 
 >--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ 
 >--with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin 
 >--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql 
 >--infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --without-berkeley-db 
 >--without-innobase '--with-comment=Official MySQL RPM'
 > Perl: This is perl, version 5.005_03 built for i386-linux


Hi!

I tested the above and it worked just fine for me.

What exactly did not work. Please check that your table is not
corrupt.


Regards,

Sinisa

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
  /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
  /*/^^^\*\^^^
 /*/ \*\Developers Team

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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

[shawn]

>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 break.
>How-To-Repeat:
Easy way:
mysql mysql
select * from users order by rand() limit 10
On my database machine using the latest stable binray release it fails.
>Fix:
None known

>Submitter-Id:  
>Originator:Shawn Wallace
>Organization:
Justweb Inc.
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:  Problem combining order by rand() with limit
>Severity:  serious
>Priority:  medium
>Category:  mysql
>Class: sw-bug
>Release:   mysql-3.23.35 (Official MySQL RPM)
>Server: /usr/bin/mysqladmin  Ver 8.18 Distrib 3.23.35, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  3.23.35
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 5 hours 56 min 1 sec

Threads: 3  Questions: 160022  Slow queries: 0  Opens: 106  Flush tables: 1  Open 
tables: 100 Queries per second avg: 7.491
>Environment:

System: Linux darthvader 2.2.18pre21-RAID #2 Wed Feb 14 17:21:03 EST 2001 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.2/specs
gcc version 2.95.2 2220 (Debian GNU/Linux)
Compilation info: CC='egcs'  CFLAGS='-O6 -fomit-frame-pointer -mpentium'  CXX='egcs'  
CXXFLAGS='-O6 -fomit-frame-pointer  -felide-constructors 
-fno-exceptions -fno-rtti -mpentium'  LDFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Mar 12 13:22 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x1 root root   887712 Jan 15 15:06 /lib/libc-2.1.3.so
-rw-r--r--1 root root  2089496 Jan 15 15:07 /usr/lib/libc.a
-rw-r--r--1 root root  178 Jan 15 15:07 /usr/lib/libc.so
Configure command: ./configure  --disable-shared --with-mysqld-ldflags=-all-static 
--with-client-ldflags=-all-static --enable-assembler --with-mysqld-user=mysql 
--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ 
--with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin 
--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql 
--infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --without-berkeley-db 
--without-innobase '--with-comment=Official MySQL RPM'
Perl: This is perl, version 5.005_03 built for i386-linux

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

order by RAND()

[Cisco]

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 another way of doing this?

I was thinking of doing something like this ::

  select column1, column2, floor(count(*) * rand()) as randRecord from 
girls g1, girls g2 where g1.randRecord = g2.girlPicId;

but that doesn't seem to work.. and mysql doesn't support subqueries.. :( 
any ideas? (without telling me to upgrade because it's not my server)

Thanks in advance!!

Cisk
  - http://www.txraves.org/djcisk/


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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

[Jeffrey D. Wheelhouse]

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 through an
>index O(random-nearest_id) where his does it O(1).  And could it
>potentially loop infinitely?

Based on my admittedly pathetic understanding of B-trees and database 
indexes, I *think* Carsten's approach is O(lg n) on the number of rows.  My 
approach is O(M*n) on the number of rows, where M is a pretty lightweight 
access to nab the key.  The "LIMIT $rand, 1" approach is O(D*n/2) on the 
number of rows over time, but D is a nasty I/O hit to slurp the whole row 
into the resultset.

The only case where Carsten's approach and mine would converge would be if 
you were using a query where no index could be applied.  Then they'd both 
be stuck at O(N) on the number of rows.

I am curious whether "(@rand:=@rand-1)+id=id" can be optimized to remove 
the table reference (id) without having the query optimizer decide it only 
needs to run once.  That might shave a good bit off of M.

In a case like this, it would be handy to have a ROW() function that tracks 
the running counter being used to generate the "X rows in set." 
statistic.  But such a thing would probably be of limited utility.

At 11:28PM 2/10/2001 -0800, Stephen Waits wrote:
>Carsten's approach is one of those "duh" things I don't understand why I
>hadn't thought of it.?

Likewise.  It's a good reminder that clever solutions don't always come 
from linear thinking.  Thanks Carsten!

Jeff


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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

[Stephen Waits]

"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 more
and I think I understand what it's doing I see the performance
implications.

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 through an
index O(random-nearest_id) where his does it O(1).  And could it
potentially loop infinitely?

--Steve

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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

[Stephen Waits]

"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's a rather nasty
> sit-'n-spin loop.  Instead of being linear with the size of the random
> number, it's linear with the size of the table, but the constant multiplier
> is much smaller.  For all but the largest and/or simplest tables, that
> should be a win.

This is broken for me - but, I've learned you can actually set variables
in SQL which just opened a HUGE door for me :)  I'm going to study more
about this ASAP.  ***Thanks for the suggestion.***  I get the following:

mysql> select * from table where (@rand:=@rand-1)+id=id;
ERROR 1064: You have an error in your SQL syntax near 'table where
(@rand:=@rand-1)+id=id' at line 1

> By my measurements, this is a good 3x faster than the LIMIT $rand, 1
> approach on my test table.  But if "id" is indexed, Carsten's no-calc
> approach still blows it away.

Carsten's approach is one of those "duh" things I don't understand why I
hadn't thought of it.  It's fast, and very closely approximates what I
want to do.

> Is it possible to do a "fair" match without incurring at least one full
> pass through the table?

This is "the question" isn't it.  At a minimum, you must know the # of
rows you have to choose from; depending on your indexes this may not
require a full pass through.  But then you must (ideally) randomly
access any single row in that set.

So maybe it cannot be done under MySQL..  What about Oracle, MS SQL, and
Postgres?

Thanks,
Steve

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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

[Jeffrey D. Wheelhouse]

Hmm, no reading comprehension points for me.  I managed to read Steve's 
message the first time without realizing that he only wanted one row.

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's a rather nasty 
sit-'n-spin loop.  Instead of being linear with the size of the random 
number, it's linear with the size of the table, but the constant multiplier 
is much smaller.  For all but the largest and/or simplest tables, that 
should be a win.

By my measurements, this is a good 3x faster than the LIMIT $rand, 1 
approach on my test table.  But if "id" is indexed, Carsten's no-calc 
approach still blows it away.

Is it possible to do a "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 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 other ways
> > I've thrown around but none are "elegant".
> >
> > One is, if a table has an id # column, like "id int unsigned not null
> > auto_increment", I could do this:
> >
> > select max(id) from table;
> > $random_number = ...
> > select * from table where id=$random_number;
>
>How about
> select * from table
> where id>$random_number
> order by id
> limit 1;
>
>(note that I'm using '>' rather than '='). This should always work,
>and be pretty fast. There is a caveat, tho': this won't work if
>you need "exact randomness", i.e. certain records will have a
>better chance of being selected than others. This gets worse,
>the larger "holes" are in sets of deleted id's.
>
>/ Carsten
>--
>Carsten H. Pedersen
>keeper and maintainer of the bitbybit.dk MySQL FAQ
>http://www.bitbybit.dk/mysqlfaq
>
>
> >
> > This is very fast (assuming the id field is a unique index).  But it has
> > the problem that if records have been deleted I might get a 0-row
> > response.  It also does not work if I want to limit to a particular
> > category, for instance "where category='women'" or something.
> >
> > I could do this too:
> >
> > select count(*) from table;
> > $random_number = ...
> > select * from table limit $random_number,1;
> >
> > This has the benefit of always working but the speed, though faster than
> > the "order by rand()" method, remains unacceptable.  The speed seems
> > linear with regard to the size of $random_number; which is probably
> > obvious to you.
> >
> > So I've experimented with several other things:
> >
> > select * from table where limit rand(),1;
> > select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
> > .. and it only gets uglier from -- these are all not accepted by MySQL.
> >
> > MySQL does not allow for subqueries which is another way it could possibly
> > be accomplished.  In the end, I'll just use what works, no matter the
> > speed.
> >
> > BUT, I'd love to hear what other people have done to solve this problem!
> >
> > Thanks,
> > Steve
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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

[Robert Barrington]

";
print $row[col2];

?>

Robert B. Barrington

GetMart Commercial Ecom: Web Administrator
http://weddinginlasvegas.com/ 
http://getmart.com/ 
[EMAIL PROTECTED]
Vegas Vista Productions
3172 North Rainbow Boulevard
Suite 326
Las Vegas, Nevada 89108-4534
Telephone: (702)656-1027
Facsimile: (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
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 other ways
I've thrown around but none are "elegant".

One is, if a table has an id # column, like "id int unsigned not null
auto_increment", I could do this:

select max(id) from table;
$random_number = ...
select * from table where id=$random_number;

This is very fast (assuming the id field is a unique index).  But it has
the problem that if records have been deleted I might get a 0-row
response.  It also does not work if I want to limit to a particular
category, for instance "where category='women'" or something.

I could do this too:

select count(*) from table;
$random_number = ...
select * from table limit $random_number,1;

This has the benefit of always working but the speed, though faster than
the "order by rand()" method, remains unacceptable.  The speed seems
linear with regard to the size of $random_number; which is probably
obvious to you.

So I've experimented with several other things:

select * from table where limit rand(),1;
select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
.. and it only gets uglier from -- these are all not accepted by MySQL.

MySQL does not allow for subqueries which is another way it could possibly
be accomplished.  In the end, I'll just use what works, no matter the
speed.

BUT, I'd love to hear what other people have done to solve this problem!

Thanks,
Steve


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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

[Carsten H. Pedersen]

> 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 other ways
> I've thrown around but none are "elegant".
>
> One is, if a table has an id # column, like "id int unsigned not null
> auto_increment", I could do this:
>
> select max(id) from table;
> $random_number = ...
> select * from table where id=$random_number;

How about
select * from table
where id>$random_number
order by id
limit 1;

(note that I'm using '>' rather than '='). This should always work,
and be pretty fast. There is a caveat, tho': this won't work if
you need "exact randomness", i.e. certain records will have a
better chance of being selected than others. This gets worse,
the larger "holes" are in sets of deleted id's.

/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq


>
> This is very fast (assuming the id field is a unique index).  But it has
> the problem that if records have been deleted I might get a 0-row
> response.  It also does not work if I want to limit to a particular
> category, for instance "where category='women'" or something.
>
> I could do this too:
>
> select count(*) from table;
> $random_number = ...
> select * from table limit $random_number,1;
>
> This has the benefit of always working but the speed, though faster than
> the "order by rand()" method, remains unacceptable.  The speed seems
> linear with regard to the size of $random_number; which is probably
> obvious to you.
>
> So I've experimented with several other things:
>
> select * from table where limit rand(),1;
> select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
> .. and it only gets uglier from -- these are all not accepted by MySQL.
>
> MySQL does not allow for subqueries which is another way it could possibly
> be accomplished.  In the end, I'll just use what works, no matter the
> speed.
>
> BUT, I'd love to hear what other people have done to solve this problem!
>
> Thanks,
> Steve
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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

[Jeffrey D. Wheelhouse]

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

Jeff

At 12:12 PM 2/10/2001 -0800, Stephen Waits wrote:

>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 other ways
>I've thrown around but none are "elegant".
>
>One is, if a table has an id # column, like "id int unsigned not null
>auto_increment", I could do this:
>
>select max(id) from table;
>$random_number = ...
>select * from table where id=$random_number;
>
>This is very fast (assuming the id field is a unique index).  But it has
>the problem that if records have been deleted I might get a 0-row
>response.  It also does not work if I want to limit to a particular
>category, for instance "where category='women'" or something.
>
>I could do this too:
>
>select count(*) from table;
>$random_number = ...
>select * from table limit $random_number,1;
>
>This has the benefit of always working but the speed, though faster than
>the "order by rand()" method, remains unacceptable.  The speed seems
>linear with regard to the size of $random_number; which is probably
>obvious to you.
>
>So I've experimented with several other things:
>
>select * from table where limit rand(),1;
>select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
>.. and it only gets uglier from -- these are all not accepted by MySQL.
>
>MySQL does not allow for subqueries which is another way it could possibly
>be accomplished.  In the end, I'll just use what works, no matter the
>speed.
>
>BUT, I'd love to hear what other people have done to solve this problem!
>
>Thanks,
>Steve
>
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

ORDER BY RAND() Too Slow! Alternatives?

[Stephen Waits]

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 other ways
I've thrown around but none are "elegant".

One is, if a table has an id # column, like "id int unsigned not null
auto_increment", I could do this:

select max(id) from table;
$random_number = ...
select * from table where id=$random_number;

This is very fast (assuming the id field is a unique index).  But it has
the problem that if records have been deleted I might get a 0-row
response.  It also does not work if I want to limit to a particular
category, for instance "where category='women'" or something.

I could do this too:

select count(*) from table;
$random_number = ...
select * from table limit $random_number,1;

This has the benefit of always working but the speed, though faster than
the "order by rand()" method, remains unacceptable.  The speed seems
linear with regard to the size of $random_number; which is probably
obvious to you.

So I've experimented with several other things:

select * from table where limit rand(),1;
select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
.. and it only gets uglier from -- these are all not accepted by MySQL.

MySQL does not allow for subqueries which is another way it could possibly
be accomplished.  In the end, I'll just use what works, no matter the
speed.

BUT, I'd love to hear what other people have done to solve this problem!

Thanks,
Steve


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php