Re: Age old order by rand() issue
I have seen nicer fast random row implement, but that will work. Do you happen to have a snip of it, the one I have seems to lean pretty heavy as far as I can tell, and on occasion, though rare, also sends me an empty result set. -- Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Age old order by rand() issue
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]
Age old order by rand() issue
List search seems to return 0 results, and I am a bit stumped. Getting a more optimized order by random with 1 record... I found a snip online that works, but seems to return an empty on occasion, and I am not sure why: SELECT storage_path, image_md5, id FROM images JOIN (SELECT CEIL(RAND() * (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]
Re: Age old order by rand() issue
Hi, Responses inline On Thu, May 1, 2008 at 3:11 PM, Scott Haneda [EMAIL PROTECTED] wrote: List search seems to return 0 results, and I am a bit stumped. Getting a more optimized order by random with 1 record... I found a snip online that works, but seems to return an empty on 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]
Re: Age old order by rand() issue
SELECT storage_path, image_md5, id FROM images JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved = 1)) AS id) AS r2 USING (id) WHERE approved = 1; I really do not get this, SELECT CEIL(RAND() will always return 1 will it not? Any idea why I get an empty 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]
implementation of SELECT ... ORDER BY RAND() LIMIT 1
2007/2/7, Jos Elkink [EMAIL PROTECTED]: Hi all, I have a question about the combination of RAND and LIMIT 1. If I have a query like: SELECT ... ORDER BY RAND() LIMIT 1 with the ... replaced with a normal query on one table. How is this implemented? Is this optimized for the fact 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]
implementation of SELECT ... ORDER BY RAND() LIMIT 1
Hi all, I have a question about the combination of RAND and LIMIT 1. If I have a query like: SELECT ... ORDER BY RAND() LIMIT 1 with the ... replaced with a normal query on one table. How is this implemented? Is this optimized for the fact that it only needs one entry? And what about when 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: implementation of SELECT ... ORDER BY RAND() LIMIT 1
I have a question about the combination of RAND and LIMIT 1. If I have a query like: SELECT ... ORDER BY RAND() LIMIT 1 with the ... replaced with a normal query on one table. How is this implemented? Is this optimized for the fact that it only needs one entry? Try prefixing your query with 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]
Re: ORDER BY RAND() gives me duplicate rows sometimes
Add DISTINCT(primary_key) in your query? Regards Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER BY RAND() gives me duplicate rows sometimes
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]
ORDER BY RAND() gives me duplicate rows sometimes
I am using this query to pull three random comments from a table: SELECT *, DATE_FORMAT(created_on, '%b %D') as date_format FROM comments ORDER BY RAND() LIMIT 3; The problem is that sometimes, I get two of the same comment. How can I refine this query to give me 3 unique/distinct ones? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY RAND() gives me duplicate rows sometimes
On 2006-11-09 Daevid Vincent wrote: I am using this query to pull three random comments from a table: SELECT *, DATE_FORMAT(created_on, '%b %D') as date_format FROM comments ORDER BY RAND() LIMIT 3; The problem is that sometimes, I get two 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]
Making ORDER BY RAND() more random [resend]
is there a way to make ORDER BY RAND() at bit more spontaneous ? I am using it in a php statement: $sql = SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.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]
Re: Making ORDER BY RAND() more random [resend]
change the seed on the random function. http://dev.mysql.com/doc/mysql/en/mathematical-functions.html On Mon, 2005-10-03 at 08:33 -0700, Graham Anderson wrote: is there a way to make ORDER BY RAND() at bit more spontaneous ? I am using it in a php statement: $sql = 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]
Re: Making ORDER BY RAND() more random [resend]
Graham Anderson wrote: is there a way to make ORDER BY RAND() a bit more spontaneous ? I am using it in a php statement: $sql = SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.picture, media.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]
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]
Making ORDER BY RAND() more random
is there a way to make ORDER BY RAND() at bit more spontaneous ? I am using it in a php statement: $sql = SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.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()
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()
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
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
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, 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
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]
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]
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]
RE: group by order by rand() problem
Try seeding your rand. Tried. It doesn't work. The select shows always the same records but in different order: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); first call ++-+---+ | id | user_id | title | ++-+---+ | 1 | 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
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]
ORDER BY RAND() performance
Hi all, I am using MySQL 4.0.x to run a community website which has (among other things) over 19,000 pictures. There is a page that selects 30 random thumbnails. I have noticed that the performance of ORDER BY RAND() on this table has a significant impact on performace. I have all 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]
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]
Re: ORDER BY RAND() performance
[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
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
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 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
Re: ORDER BY RAND() performance
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
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 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
Re: ORDER BY RAND() performance
Ray wrote: an alternative to the order by rand() with large record sets is to pick a random starting point limit $randPoint, 30 don't know if its a viable solution to your situation, but it limits you to 2 querys (row count, fetch) rather then the 30 (fetch 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 still wondering if I should post my original question about whether ORDER BY RAND() will be optimized anytime soon to the internals list... I don't want
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]
RE: ORDER BY RAND() performance
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
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 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]
order by rand()
Hey all... I did a quick check of the archives, but didn't see anything recent about this... I use PHP and mySQL, and am supposed to be able to randomize the order in which the rows are held when retrieved using ORDER BY RAND(). This isn't working for me... it's on a third-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 -=-=-=-=-
random order with rand() is foreseeing
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 13287 http://bugs.php.net/bug.php?id=13287edit=1 Fix: Rewrote the random routines. I think this is not only a small bug, it is a potentially security hole if someone using this type of random results in a security environment. Category: mysql Class: sw-bug Release: mysql-4.0.2-alpha (Source distribution) and mysql-3.x.x System: Linux tratos 2.4.18-6mdk #1 Fri Mar 15 02:59:08 CET 2002 i686 unknown Architecture: i686 - 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
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
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
RE: ORDER BY RAND() not working
[snip] I'm running PHP 4.1.2 and MySQL 3.23.39. I have a simple query that works if I telnet into MySQL and run the query manually. If I try and use it in a PHP page, I get the Supplied argument is not a valid MySQL result resource error. However, if I take out the ORDER BY RAND() 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
RE: ORDER BY RAND() not working
What does your PHP code look like ? It might be a syntax problem inside PHP -Message d'origine- De : Jamie Tibbetts [mailto:[EMAIL PROTECTED]] Envoye : jeudi 11 juillet 2002 18:22 A : [EMAIL PROTECTED] Objet : ORDER BY RAND() not working I'm running PHP 4.1.2 and MySQL 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
$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
[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 . nbsp . $row-ClientName . br);
}
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
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
RE: order by rand() question
I don't think the answer has changed since last week :-)
IMHO it's not really slow and inefficient, anyway - this script:
---start---
?php
$db = mysql_connect(localhost, root, :-));
mysql_select_db(Playground, $db);
mysql_query(CREATE TABLE mytable (myfield INTEGER UNSIGNED, PRIMARY KEY
myfield)));
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 |
+-+
|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
Re: order by rand() question
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
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
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
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
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
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
Question about order by rand()
I can't seem to find a specificatoin on order by rand() and have a question. Lets say a table has 10,000 rows in it, and I want to get one row that matches so I do a select * from table where number=1 order by rand() limit 1; What if 100 rows match? Does it first get all 100 rows and 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
Re: Question about order by rand()
On Wed, Jun 13, 2001 at 03:25:55PM -0600, Matt Heaton wrote: I can't seem to find a specificatoin on order by rand() and have a question. Lets say a table has 10,000 rows in it, and I want to get one row that matches so I do a select * from table 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
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
[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: 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
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: 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
Re: ORDER BY RAND() Too Slow! Alternatives?
At 11:39 PM 2/10/2001 -0800, Stephen Waits wrote: Never mind on the "it doesn't work on my system" more like it didn't work on my brain :) Works fine. Oh, phew. Theoretically it could be as fast as Carsten's method couldn't it? If it hit a record on the first shot? Otherwise it's pounding 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?
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
RE: ORDER BY RAND() Too Slow! Alternatives?
Hi there, In the quest to get a random row from a table, "order by rand()" has proven too inefficient and slow. It's slow because MySQL apparently selects ALL rows into memory, then randomly shuffles ALL of them, then gives you the first one - very 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?
?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 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?
"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
