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
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
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
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,
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
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
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
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
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]
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
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
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
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
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
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
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
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
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
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
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
Alle 21:57, giovedì 1 aprile 2004, Michael Stassen ha scritto:
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,
| 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
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]
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
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
-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
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
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
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
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
...
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
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
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
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
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
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
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
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
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
Description:
The problem is affected in combination with 'order by' and
'rand()'.
Please check the report under:
http://bugs.php.net/bug.php?id=13287edit=1
How-To-Repeat:
You can compare my results with the method which is reported in
php
bug report number
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
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
[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
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
$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
[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
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
)));
for ($foo=1; $foo=1; $foo++) {
$s = INSERT INTO mytable SET myfield=. $foo;
mysql_query($s);
}
?
end
produces a table with 1 rows each containing an integer. Then we do:
mysql SELECT * FROM mytable ORDER BY RAND() LIMIT 5;
+-+
| myfield
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
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
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
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
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
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
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
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
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
[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
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
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
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
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
?php
$query = "SELECT col1, col2 FROM the_table ORDER BY RAND() LIMIT 1";
$result = mysql_query($query) or die("could not query");
$row = mysql_fetch_array($result);
print $row[col1];
print "P";
print $row[col2];
?
Robert B. Barrington
GetMart Commercia
"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
64 matches
Mail list logo
