Random SELECT on subset
Dear MySQL-ers, Using MySQL 4.1.20, I'm trying to do a complex query on a subset; well, complex to me, that is. :) In Perl, I'm trying to get 4 random entries from a subset WHERE processed = '1' and columnId is unique. Like so: $sth = $dbh-prepare (SELECT columnId FROM queue WHERE columnId = (SELECT FLOOR(MAX(columnId) * RAND()) FROM queue) IN (SELECT columnId FROM queue WHERE processed = '1') ORDER BY columnId LIMIT 4); Seems to work fine. Only problem is, every once in a while I only get 3 numbers returned (or none at all, when I set LIMIT 1). Something to do with FLOOR and RAND, I reckon; but if I knew exactly what the problem was, I wouldn't asking. :) So, anyone here know what I'm doing wrong in my query? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random SELECT on subset
Mark wrote: Dear MySQL-ers, Using MySQL 4.1.20, I'm trying to do a complex query on a subset; well, complex to me, that is. :) In Perl, I'm trying to get 4 random entries from a subset WHERE processed = '1' and columnId is unique. Like so: $sth = $dbh-prepare (SELECT columnId FROM queue WHERE columnId = (SELECT FLOOR(MAX(columnId) * RAND()) FROM queue) IN (SELECT columnId FROM queue WHERE processed = '1') ORDER BY columnId LIMIT 4); I'm not even sure what you're trying to get here! Could you provide some sample data (5 rows) and what you want the query to return? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Random SELECT on subset
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 3:55 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset In Perl, I'm trying to get 4 random entries from a subset WHERE processed = '1' and columnId is unique. Like so: $sth = $dbh-prepare (SELECT columnId FROM queue WHERE columnId = (SELECT FLOOR(MAX(columnId) * RAND()) FROM queue) IN (SELECT columnId FROM queue WHERE processed = '1') ORDER BY columnId LIMIT 4); I'm not even sure what you're trying to get here! Could you provide some sample data (5 rows) and what you want the query to return? Well, there are a great many colums in the real rows, but say they are like this: columnID, picturename, processed, 1 name11 2 name20 3 name31 4 name41 5 name51 6 name60 7 name71 Then I want to select 4 random columnIDs, but only from the subset WHERE processed = '1' (so, from the group 1,3,4,5,7). Thanks. I appreciate your help. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random SELECT on subset
Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 3:55 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset In Perl, I'm trying to get 4 random entries from a subset WHERE processed = '1' and columnId is unique. Like so: $sth = $dbh-prepare (SELECT columnId FROM queue WHERE columnId = (SELECT FLOOR(MAX(columnId) * RAND()) FROM queue) IN (SELECT columnId FROM queue WHERE processed = '1') ORDER BY columnId LIMIT 4); I'm not even sure what you're trying to get here! Could you provide some sample data (5 rows) and what you want the query to return? Well, there are a great many colums in the real rows, but say they are like this: columnID, picturename, processed, 1 name11 2 name20 3 name31 4 name41 5 name51 6 name60 7 name71 Then I want to select 4 random columnIDs, but only from the subset WHERE processed = '1' (so, from the group 1,3,4,5,7). This query should do what you want: SELECT columnID from table where process='1' order by rand() limit 4; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Random SELECT on subset
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 4:18 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Then I want to select 4 random columnIDs, but only from the subset WHERE processed = '1' (so, from the group 1,3,4,5,7). This query should do what you want: SELECT columnID from table where process='1' order by rand() limit 4; What? And all the FLOOR stuff can go? :) That sounds too simple, lol. Well, thanks anyway; I'll go try it. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random SELECT on subset
Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 4:18 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Then I want to select 4 random columnIDs, but only from the subset WHERE processed = '1' (so, from the group 1,3,4,5,7). This query should do what you want: SELECT columnID from table where process='1' order by rand() limit 4; What? And all the FLOOR stuff can go? :) That sounds too simple, lol. (just noticed a typo, my query should be where processed='1' not process='1').. Based on what you've said that should be the whole query yes :) Of course make sure it provides the information you want.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Random SELECT on subset
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 4:54 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Hmm, this still does not do what I want: SELECT columnId FROM queue WHERE processed = '1' ORDER BY RAND() LIMIT 4 Thanks to you, the query has been greatly simplified, but the result is still the same: every once in a while (like if I run this twenty times in a row), I only get 3 items returned, or even 2! I'm guessing the RAND() function occassionally rounds the number to a columnId (bigint) that does not match the subset of the WHERE clause? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random SELECT on subset
Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 4:54 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Hmm, this still does not do what I want: SELECT columnId FROM queue WHERE processed = '1' ORDER BY RAND() LIMIT 4 Thanks to you, the query has been greatly simplified, but the result is still the same: every once in a while (like if I run this twenty times in a row), I only get 3 items returned, or even 2! And how many have 'processed=1' at that stage? I highly doubt it's the rand() doing it - it's your data changing. If you only have 2 unprocessed items in the queue, then mysql can only ever retrieve 2 results no matter what limit you put on it. The WHERE part of the query is processed before the limit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Random SELECT on subset
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 6:32 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Thanks to you, the query has been greatly simplified, but the result is still the same: every once in a while (like if I run this twenty times in a row), I only get 3 items returned, or even 2! And how many have 'processed=1' at that stage? I highly doubt it's the rand() doing it - it's your data changing. If you only have 2 unprocessed items in the queue, then mysql can only ever retrieve 2 results no matter what limit you put on it. The WHERE part of the query is processed before the limit. The data is NOT changing, of course. :) Just a small, fixed test table and a test.pl prog to run this off a prompt. Same table, same test prog, ran several times in a row. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random SELECT on subset
Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 6:32 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Thanks to you, the query has been greatly simplified, but the result is still the same: every once in a while (like if I run this twenty times in a row), I only get 3 items returned, or even 2! And how many have 'processed=1' at that stage? I highly doubt it's the rand() doing it - it's your data changing. If you only have 2 unprocessed items in the queue, then mysql can only ever retrieve 2 results no matter what limit you put on it. The WHERE part of the query is processed before the limit. The data is NOT changing, of course. :) Just a small, fixed test table and a test.pl prog to run this off a prompt. Same table, same test prog, ran several times in a row. Easiest way to tell is add an extra query: select count(*) from tablename where processed='1'; ..or even: select * from tablename where processed='1'; so you can compare what the rand() is doing compared to the table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
On 3/29/2006 2:10 PM, Jorrit Kronjee wrote: It seems you are running in to Bug #7209: http://bugs.mysql.com/bug.php?id=7209 This is fixed in 5.0.19 now. Best regards Mark Mark, Apparently so, thanks for the hint! We'll try to upgrade as soon as possible. I'll supply the mailing list with the results of the upgrade. Mark, We've been testing it over the weekend and it seems that the bugs we ran into before completely disappeared. Thanks for your support! Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Jorrit Kronjee wrote: On 3/29/2006 2:10 PM, Jorrit Kronjee wrote Mark, Apparently so, thanks for the hint! We'll try to upgrade as soon as possible. I'll supply the mailing list with the results of the upgrade. Mark, We've been testing it over the weekend and it seems that the bugs we ran into before completely disappeared. Thanks for your support! Jorrit Hi Jorrit, Great news - that bug was one that we tried for a long time to replicate (as can be seen from the bug report), eventually I managed to repeat it a few months ago, and we got the fix turned around fairly quickly - so it's a fairly obvious one every time I see it now ;) Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
[EMAIL PROTECTED] wrote: is this relevant ? We've been looking at connection graphs, but MySQL doesn't seem to reach that limit. However, these are timely based measurements, so it could've peaked in between, although highly unlikely. I'm not very comfortable tweaking these values in a production environment while I have no indication that it could solve the problem. Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Hi Jorrit, Jorrit Kronjee wrote: [EMAIL PROTECTED] wrote: is this relevant ? We've been looking at connection graphs, but MySQL doesn't seem to reach that limit. However, these are timely based measurements, so it could've peaked in between, although highly unlikely. I'm not very comfortable tweaking these values in a production environment while I have no indication that it could solve the problem. Jorrit It seems you are running in to Bug #7209: http://bugs.mysql.com/bug.php?id=7209 This is fixed in 5.0.19 now. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Mark Leith wrote: Hi Jorrit, Jorrit Kronjee wrote: [EMAIL PROTECTED] wrote: is this relevant ? We've been looking at connection graphs, but MySQL doesn't seem to reach that limit. However, these are timely based measurements, so it could've peaked in between, although highly unlikely. I'm not very comfortable tweaking these values in a production environment while I have no indication that it could solve the problem. Jorrit It seems you are running in to Bug #7209: http://bugs.mysql.com/bug.php?id=7209 This is fixed in 5.0.19 now. Best regards Mark Mark, Apparently so, thanks for the hint! We'll try to upgrade as soon as possible. I'll supply the mailing list with the results of the upgrade. Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random 'select permission denied' since upgrade to 5.0.18
Hello list, Recently we upgraded from 3.23.3 to 5.0.18 and started noticing some weird behaviour. We have update scripts running on a regular interval and some of these scripts randomily exited with MySQL error: SELECT command denied to user 'user'@'host' for table 'example' However, this error message only appears sporadically. Usually these scripts run without any problems. tcpdump shows nothing more than we already know. I see the error message appear on different queries. We have the server running on Gentoo Linux (mysql-5.0.18-r30) as a master (with one slave on another Gentoo machine). Any ideas to debug this problem any further? Thanks in advance, Jorrit By the way, there's been a previous posting to this list describing similar problems (no solution though): http://marc.theaimsgroup.com/?l=mysqlm=113050497323853w=2 -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Keith, Thanks for answering me. Yes I did. Nothing in the MySQL log either. Keep in mind that these errors only sometimes show up and usually in the middle of a few queries in a row (while permissions are set for the whole table). Because the script runs fine most of the time, I don't think privilege settings are the key here. Could this message appear when, for instance, a maximum amount of threads has been spawned or MySQL has reached its connection limit? Jorrit Keith Roberts wrote: Check your mysql log and see if it says anything about not being able to use the new password format. Did run mysql_fix_privilege_tables to update your mysql passwords in the mysql privileges database? Regards Keith In theory, theory and practice are the same; in practice they are not. On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Random 'select permission denied' since upgrade to 5.0.18 Hello list, Recently we upgraded from 3.23.3 to 5.0.18 and started noticing some weird behaviour. We have update scripts running on a regular interval and some of these scripts randomily exited with MySQL error: SELECT command denied to user 'user'@'host' for table 'example' However, this error message only appears sporadically. Usually these scripts run without any problems. tcpdump shows nothing more than we already know. I see the error message appear on different queries. We have the server running on Gentoo Linux (mysql-5.0.18-r30) as a master (with one slave on another Gentoo machine). Any ideas to debug this problem any further? Thanks in advance, Jorrit By the way, there's been a previous posting to this list describing similar problems (no solution though): http://marc.theaimsgroup.com/?l=mysqlm=113050497323853w=2 -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 snip Could this message appear when, for instance, a maximum amount of threads has been spawned or MySQL has reached its connection limit? Possible - what are your settings for the relevant mysql server variables? You could try something like: show variables like max% \G I'm not really sure what all the server variables do, but they may be relevant to your problem. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
[EMAIL PROTECTED] wrote: On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 snip Could this message appear when, for instance, a maximum amount of threads has been spawned or MySQL has reached its connection limit? Possible - what are your settings for the relevant mysql server variables? You could try something like: show variables like max% \G I'm not really sure what all the server variables do, but they may be relevant to your problem. Keith Keith, Here's the output: mysql show variables like max% \G *** 1. row *** Variable_name: max_allowed_packet Value: 1047552 *** 2. row *** Variable_name: max_binlog_cache_size Value: 4294967295 *** 3. row *** Variable_name: max_binlog_size Value: 1073741824 *** 4. row *** Variable_name: max_connect_errors Value: 10 *** 5. row *** Variable_name: max_connections Value: 300 *** 6. row *** Variable_name: max_delayed_threads Value: 20 *** 7. row *** Variable_name: max_error_count Value: 64 *** 8. row *** Variable_name: max_heap_table_size Value: 16777216 *** 9. row *** Variable_name: max_insert_delayed_threads Value: 20 *** 10. row *** Variable_name: max_join_size Value: 4294967295 *** 11. row *** Variable_name: max_length_for_sort_data Value: 1024 *** 12. row *** Variable_name: max_relay_log_size Value: 0 *** 13. row *** Variable_name: max_seeks_for_key Value: 4294967295 *** 14. row *** Variable_name: max_sort_length Value: 1024 *** 15. row *** Variable_name: max_sp_recursion_depth Value: 0 *** 16. row *** Variable_name: max_tmp_tables Value: 32 *** 17. row *** Variable_name: max_user_connections Value: 0 *** 18. row *** Variable_name: max_write_lock_count Value: 4294967295 18 rows in set (0.00 sec) -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
is your access control by hostname or IPnumber? if hostname you could be having transient DNS issues - where the IPnumber on the client connect can't be resolved into the permitted hostname (fast enough). try using IPnumber in the access control and see if the problem goes away - if it does you'll want to look into your inverse-map DNS issues. I checked it. It's by IP number. However, I wonder, could this be the case even after multiple queries in the same TCP session? Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 [EMAIL PROTECTED] wrote: On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 snip Could this message appear when, for instance, a maximum amount of threads has been spawned or MySQL has reached its connection limit? Possible - what are your settings for the relevant mysql server variables? You could try something like: show variables like max% \G I'm not really sure what all the server variables do, but they may be relevant to your problem. Keith Keith, Here's the output: mysql show variables like max% \G *** 1. row *** Variable_name: max_allowed_packet Value: 1047552 *** 2. row *** Variable_name: max_binlog_cache_size Value: 4294967295 *** 3. row *** Variable_name: max_binlog_size Value: 1073741824 *** 4. row *** Variable_name: max_connect_errors Value: 10 *** 5. row *** Variable_name: max_connections Value: 300 *** 6. row *** Variable_name: max_delayed_threads Value: 20 is this relevant ? *** 7. row *** Variable_name: max_error_count Value: 64 *** 8. row *** Variable_name: max_heap_table_size Value: 16777216 *** 9. row *** Variable_name: max_insert_delayed_threads Value: 20 ditto *** 10. row *** Variable_name: max_join_size Value: 4294967295 *** 11. row *** Variable_name: max_length_for_sort_data Value: 1024 *** 12. row *** Variable_name: max_relay_log_size Value: 0 *** 13. row *** Variable_name: max_seeks_for_key Value: 4294967295 *** 14. row *** Variable_name: max_sort_length Value: 1024 *** 15. row *** Variable_name: max_sp_recursion_depth Value: 0 *** 16. row *** Variable_name: max_tmp_tables Value: 32 *** 17. row *** Variable_name: max_user_connections Value: 0 *** 18. row *** Variable_name: max_write_lock_count Value: 4294967295 18 rows in set (0.00 sec) -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- 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: random select 1 record
At 02:08 AM 9/20/2002, you wrote: Hi all, anyone knows a good method to retrieve 1 random selevted record out of thouseands? right now I'm using select ID from table ORDER BY rand() LIMIT 1 however, this seems like a very slow method, like mysql first organizes a few thousand records in a random 1 and only returns 1 Any help would be great! thx. Wilbert Wilbert, It can't be done using just SQL statements (unless you cheat). It all depends on how random the results have to be. The long way: You need to write code in PHP or whatever language you're using. As you've found out, the Order By Rand() physically sorts the table which can be extremely slow if you have thousands of rows. So the solution is to have a table with an auto incrementing column, like rcd_id. You need to retrieve the first (1) and last number of this range (90,100). Create a loop and inside this loop create a random number (say 42,132) within this range. Then check to see if Rcd_Id=that random number. If it does, then you're got your record and you can exit the loop. If the rcd_id is not found, then you need to generate another random number and try again. The reason the rcd_id isn't found is if the row was deleted. Now a lot of people might think you can simply do a select * from table where rcd_id = ceiling(90100*rand()) limit 1 but that won't work if you have holes in your rcd_id sequence. Example, say rcd_id's 10 through 19 are deleted and you have rcd_id's 1..10, and 20. Then rcd_id=20 will be picked half the time because it also gets selected if the random number is in the range 11..19. The easy way (cheat): Ok, the easiest way is to cheat.g Add a Float (or Double if you have a very large # of rows) column to your table and make it an index. Now each time a row is added, add a Rand() to that column. Now each row has a random number 0 = rand 1. To get a random row just do Select * from table where RandCol = Rand() limit 1. This will give you pretty good random results. We are assuming of course that duplicate random numbers would be quite rare and in most cases this fudge factor won't matter. This all depends on how good the Rand() function is at creating a proper distribution of random numbers. The RandCol must be indexed so it traverses the index, otherwise it will find rows at the beginning of the table every time. This method should be quite fast and reasonably random. The overhead is of course an extra column and index. I hope this helps. :) Mike - 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
random select 1 record
Hi all, anyone knows a good method to retrieve 1 random selevted record out of thouseands? right now I'm using select ID from table ORDER BY rand() LIMIT 1 however, this seems like a very slow method, like mysql first organizes a few thousand records in a random 1 and only returns 1 Any help would be great! thx. Wilbert --query, mysql--- - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [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: random select 1 record
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org) From: W. Enserink [EMAIL PROTECTED] Date: Fri, 20 Sep 2002 09:08:53 +0200 anyone knows a good method to retrieve 1 random selevted record out of thouseands? right now I'm using select ID from table ORDER BY rand() LIMIT 1 however, this seems like a very slow method, like mysql first organizes a few thousand records in a random 1 and only returns 1 Any help would be great! If you have a unique and continous index, then perhaps something like may be useful: select truncate(rand()*count(*),0) from TABLE; it was only 3 times faster than your current however. - 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
Random Select Statement - help please
Hi Roger, Thanks for your reply and I apologize for my lack of specificity on my previous email, thanks for your patience :). I have a people object that has 4 entities. name, pword, user_id, picurl This data is stored in the people table. Each user is identified by a unique user_id. I would like to create a mySQL query that will find the max user_id, use this value to calculate a random value of the user_id from [1 thru max], then return the entire row for that person(user_id) filled in with that persons(row) four entities, which I can use to populate the people object. select @max:=max(user_id) from people; select round((@max-1)*rand()+1) from people limit 1; Returns: 1 field, 1 record which is empty. select round((max(user_id)-1)*rand()+1) from people limit 1; returns: 1 field, 1 record populated with a random number between 1 and max of the user_id column. This is great, but how can I put this together, so that the query will return 4 fields, 1 record that is populated with the 4 entities for a single random person? Thanks a bunch for your help! Best regards, David ps: When you state @max: does this create a variable @max in a mysql buffer that you can use in subsequent queries? If so when does this variable expire? When you close the connection? -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 2:40 AM To: David V. Edelstein Subject: RE: Random Select Statement?? Sorry Roger, It's not working correctly. Please, how can I accomplish the following? select * from people where user_id=(get the random #) limit 1; It is difficult to help you when you don't say what the problem is... do you get an error message? Do you get the wrong result? No result? These are valid sql statements, but they may need mysql version 3.23 or later, and both statements must be run within the same session (I don't think you can use phpadmin or similar, unless multiple commands can be executed at the same time): select @max:=max(user_id) from people; select round((@max-1)*rand()+1) from people limit 1; Your own statement should also work: select round((max(user_id)-1)*rand()+1) from people limit 1; If it doesn't work, something is wrong... ;) - You must enter the statements in the mysql client - You must be connected to the correct database - You must have select privileges on the table - The table must be named 'people', and the field must be named 'user_id' -- Roger - 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: Random Select Statement - help please
* David V. Edelstein I would like to create a mySQL query that will find the max user_id, use this value to calculate a random value of the user_id from [1 thru max], then return the entire row for that person(user_id) filled in with that persons(row) four entities, which I can use to populate the people object. Because of the lack of sub-selects in mysql, I suggested using user variables: select @max:=max(user_id) from people; select round((@max-1)*rand()+1) from people limit 1; Returns: 1 field, 1 record which is empty. You should get a random number. I did not notice you wanted the entire people record, sorry. Disregard the above select-statements anyway, they are no good. :/ select round((max(user_id)-1)*rand()+1) from people limit 1; returns: 1 field, 1 record populated with a random number between 1 and max of the user_id column. This is great, but how can I put this together, so that the query will return 4 fields, 1 record that is populated with the 4 entities for a single random person? Yes, you wanted * from people... try this: select @rnd:=round((max(user_id)-1)*rand()+1) from people; select * from people where user_id = @rnd; If some user_id's are missing you can use user_id = @rnd limit 1 to get the first user_id after the missing. Thanks a bunch for your help! You're welcome! :) ps: When you state @max: does this create a variable @max in a mysql buffer that you can use in subsequent queries? Yes, the variable is saved in the server memory. If so when does this variable expire? When you close the connection? yes. :) URL: http://www.mysql.com/doc/V/a/Variables.html URL: http://www.mysql.com/doc/e/x/example-user-variables.html (This last example is similar to your problem.) -- Roger - 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: Random Select Statement - help please
Easier method: SELECT * FROM people ORDER BY RAND() LIMIT 1; -Original Message- From: David V. Edelstein [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 1:08 PM To: 'Roger Baklund' Cc: '[EMAIL PROTECTED]' Subject: Random Select Statement - help please Hi Roger, Thanks for your reply and I apologize for my lack of specificity on my previous email, thanks for your patience :). I have a people object that has 4 entities. name, pword, user_id, picurl This data is stored in the people table. Each user is identified by a unique user_id. I would like to create a mySQL query that will find the max user_id, use this value to calculate a random value of the user_id from [1 thru max], then return the entire row for that person(user_id) filled in with that persons(row) four entities, which I can use to populate the people object. select @max:=max(user_id) from people; select round((@max-1)*rand()+1) from people limit 1; Returns: 1 field, 1 record which is empty. select round((max(user_id)-1)*rand()+1) from people limit 1; returns: 1 field, 1 record populated with a random number between 1 and max of the user_id column. This is great, but how can I put this together, so that the query will return 4 fields, 1 record that is populated with the 4 entities for a single random person? Thanks a bunch for your help! Best regards, David ps: When you state @max: does this create a variable @max in a mysql buffer that you can use in subsequent queries? If so when does this variable expire? When you close the connection? -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 2:40 AM To: David V. Edelstein Subject: RE: Random Select Statement?? Sorry Roger, It's not working correctly. Please, how can I accomplish the following? select * from people where user_id=(get the random #) limit 1; It is difficult to help you when you don't say what the problem is... do you get an error message? Do you get the wrong result? No result? These are valid sql statements, but they may need mysql version 3.23 or later, and both statements must be run within the same session (I don't think you can use phpadmin or similar, unless multiple commands can be executed at the same time): select @max:=max(user_id) from people; select round((@max-1)*rand()+1) from people limit 1; Your own statement should also work: select round((max(user_id)-1)*rand()+1) from people limit 1; If it doesn't work, something is wrong... ;) - You must enter the statements in the mysql client - You must be connected to the correct database - You must have select privileges on the table - The table must be named 'people', and the field must be named 'user_id' -- Roger - 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
Random Select Statement??
I'm trying to create a select statement that will go into a table find the number of rows in the table then select a single random row and return it. I am using Java/mySQL with the mm.mysql JDBC drivers. I've been playing around with the following statement but it doesn't seem to like the MAX(user_id) in the middle of it. Can anyone advise me on the correct way to execute this? Thanks in advance! SELECT ROUND((MAX(user_id)-1)*RAND()+1) from people; David - 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: Random Select Statement??
* David V. Edelstein I'm trying to create a select statement that will go into a table find the number of rows in the table then select a single random row and return it. I am using Java/mySQL with the mm.mysql JDBC drivers. I've been playing around with the following statement but it doesn't seem to like the MAX(user_id) in the middle of it. Can anyone advise me on the correct way to execute this? Thanks in advance! SELECT ROUND((MAX(user_id)-1)*RAND()+1) from people; select @max:=max(user_id) from people; select round((@max-1)*rand()+1) from people limit 1; -- Roger - 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
random select
hi, i would like to show 5 random field from a total of 100. id is my primary key id name -- abmarcos akjohn how can i show 5 of them randomly? thanks in advance, marcos [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: random select
On Tue, 3 Apr 2001, Marcos wrote: hi, i would like to show 5 random field from a total of 100. id is my primary key id name -- abmarcos akjohn how can i show 5 of them randomly? thanks in advance, marcos When you say "5 random fields" I assume you mean "5 random rows". select * from table order by rand() limit 5; Read the SELECT from the manual (http://www.mysql.com/doc/S/E/SELECT.html) regards, thalis - 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
