Re: grouping and limiting results and rand()
On 2013-09-23 8:10 PM, Jeremiah Jester wrote: Hello, How would i go about selecting 5 random cars that are flagged as internet_special (value 1) for each dealer from a specific table? Ive tried sub selects with no luck. Here's a basic query that has my required conditional. Note that I get all unique dealers by doing a distinct(dealer_web_name). Make sense? Using mysql 5. select dealer_web_name,id,internet_special,active from inventory where internet_special=1 and active=1; Appreciate the help For ideas see "Within-group quotas" at http://www.artfulsoftware.com/infotree/queries.php. PB - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
grouping and limiting results and rand()
Hello, How would i go about selecting 5 random cars that are flagged as internet_special (value 1) for each dealer from a specific table? Ive tried sub selects with no luck. Here's a basic query that has my required conditional. Note that I get all unique dealers by doing a distinct(dealer_web_name). Make sense? Using mysql 5. select dealer_web_name,id,internet_special,active from inventory where internet_special=1 and active=1; Appreciate the help Jj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL RAND() Issues [was Re: How to Shuffle data]
Am 15.07.2011 19:40, schrieb Jan Steinman: >> From: Reindl Harald >> >> do not use any random-functions of mysql even if they exists >> http://bugs.mysql.com/bug.php?id=59253 > > So RAND() can be useful, but it is not really very random the problem is the idiotic temp table fecth a random record with it out of a tbale with few thousand of records and pray to god that your website has not much users because mysqld will bring your machine down signature.asc Description: OpenPGP digital signature
Re: MySQL RAND() Issues [was Re: How to Shuffle data]
In the last episode (Jul 15), Arthur Fuller said: > This would be sooo much simpler to solve in MS-SQL, given the function > NewID(), which is guaranteed to return a unique value. I have used this > in a few web sites and it works splendidly; something along the lines of > > SELECT TOP 10 *, NewID() FROM User_Messages ORDER BY NewID > > which is guaranteed to produce a new GUID for each row, and then order by > said values. It is not guaranteed to produce 10 results different than > the previous SELECT, but nevertheless works extremely well on a web site. > > I have Googled this and that but not yet succeeded in finding the > equivalent in the MySQL world. In theory, this should be relatively easy, > since the GUID docs are available, but I'm out of my depth here on how to > write the MySQL equivalent function. http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid The UUID() function is that you want. It generates a GUID just like NewID(), but the problem is that a GUID is only guaranteed to be unique, not random: mysql> select uuid() union select uuid() union select uuid(); +--+ | uuid() | +--+ | 11a5cfd1-af13-11e0-80f5-0019b9df7547 | | 11a5d054-af13-11e0-80f5-0019b9df7547 | | 11a5d092-af13-11e0-80f5-0019b9df7547 | +--+ (your output may vary depending on your OS'es preferred type of UUID to generate). You could use md5(uuid()), however, which hashes your unique values to get a nice large pseudorandom number: mysql> select md5(uuid()) union select md5(uuid()) union select md5(uuid()); +--+ | md5(uuid()) | +--+ | 6faefaf3f7bb9ba0d1e7a44cf6a9b1da | | 740135ab69a1825630aeaf475b39f8b8 | | 5c91a9132ad3e49e098e41d573de8e00 | +--+ -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL RAND() Issues [was Re: How to Shuffle data]
I have found this approach pretty effective: select *, rand() r from [mytable] where [condition] order by r limit 10 as long as you are aware that a random number is generated for every row in the table. - michael dykman On Fri, Jul 15, 2011 at 2:12 PM, Arthur Fuller wrote: > This would be sooo much simpler to solve in MS-SQL, given the function > NewID(), which is guaranteed to return a unique value. I have used this in a > few web sites and it works splendidly; something along the lines of > > SELECT TOP 10 *, NewID() FROM User_Messages > ORDER BY NewID > > which is guaranteed to produce a new GUID for each row, and then order by > said values. It is not guaranteed to produce 10 results different than the > previous SELECT, but nevertheless works extremely well on a web site. > > I have Googled this and that but not yet succeeded in finding the equivalent > in the MySQL world. In theory, this should be relatively easy, since the > GUID docs are available, but I'm out of my depth here on how to write the > MySQL equivalent function. > > In case anyone has devised a MySQL equivalent for the NewID() function, we > could all massively benefit from your posting of same. > > TIA, > Arthur > > On Fri, Jul 15, 2011 at 1:40 PM, Jan Steinman wrote: > >> > From: Reindl Harald >> > >> > do not use any random-functions of mysql even if they exists >> > http://bugs.mysql.com/bug.php?id=59253 >> >> Of course, it depends on the desired quality of randomness needed. >> >> I'm using RAND() to select random quotations to put at the end of emails. I >> can easily repeat the process by re-selecting the "Signature:" menu in Apple >> Mail. Problem is, I often notice that doing so cycles through several >> similar signatures in a decidedly non-random way! >> >> (You can demo this by sending email to , with a >> search term in the Subject: line.) >> >> But of course, a nuclear plant is not going to melt down because of my >> signature line. >> >> So RAND() can be useful, but it is not really very random, and should be >> used with caution. My guess is that it's using Knuth's linear congruential >> algorithm that has well-known problems, but that unfortunately has been >> hidden deep in system code libraries since "Fundamental Algorithms" was >> published in 1968. >> >> >> World events tend to be driven by loose coalitions of economic, political, >> and military interests, which function like guilds of species in an >> ecosystem. These guilds generate patterns of events that meet the interests >> of these coalitions, without there being any unity of purpose or clear plan. >> When powerful players accept they are not all-powerful, they increase their >> effectiveness, but are also able to deny and cover any responsibility for >> the adverse outcomes of those actions. -- David Holmgren >> Jan Steinman, EcoReality Co-op >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com >> >> > -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL RAND() Issues [was Re: How to Shuffle data]
This would be sooo much simpler to solve in MS-SQL, given the function NewID(), which is guaranteed to return a unique value. I have used this in a few web sites and it works splendidly; something along the lines of SELECT TOP 10 *, NewID() FROM User_Messages ORDER BY NewID which is guaranteed to produce a new GUID for each row, and then order by said values. It is not guaranteed to produce 10 results different than the previous SELECT, but nevertheless works extremely well on a web site. I have Googled this and that but not yet succeeded in finding the equivalent in the MySQL world. In theory, this should be relatively easy, since the GUID docs are available, but I'm out of my depth here on how to write the MySQL equivalent function. In case anyone has devised a MySQL equivalent for the NewID() function, we could all massively benefit from your posting of same. TIA, Arthur On Fri, Jul 15, 2011 at 1:40 PM, Jan Steinman wrote: > > From: Reindl Harald > > > > do not use any random-functions of mysql even if they exists > > http://bugs.mysql.com/bug.php?id=59253 > > Of course, it depends on the desired quality of randomness needed. > > I'm using RAND() to select random quotations to put at the end of emails. I > can easily repeat the process by re-selecting the "Signature:" menu in Apple > Mail. Problem is, I often notice that doing so cycles through several > similar signatures in a decidedly non-random way! > > (You can demo this by sending email to , with a > search term in the Subject: line.) > > But of course, a nuclear plant is not going to melt down because of my > signature line. > > So RAND() can be useful, but it is not really very random, and should be > used with caution. My guess is that it's using Knuth's linear congruential > algorithm that has well-known problems, but that unfortunately has been > hidden deep in system code libraries since "Fundamental Algorithms" was > published in 1968. > > > World events tend to be driven by loose coalitions of economic, political, > and military interests, which function like guilds of species in an > ecosystem. These guilds generate patterns of events that meet the interests > of these coalitions, without there being any unity of purpose or clear plan. > When powerful players accept they are not all-powerful, they increase their > effectiveness, but are also able to deny and cover any responsibility for > the adverse outcomes of those actions. -- David Holmgren > Jan Steinman, EcoReality Co-op > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com > >
MySQL RAND() Issues [was Re: How to Shuffle data]
> From: Reindl Harald > > do not use any random-functions of mysql even if they exists > http://bugs.mysql.com/bug.php?id=59253 Of course, it depends on the desired quality of randomness needed. I'm using RAND() to select random quotations to put at the end of emails. I can easily repeat the process by re-selecting the "Signature:" menu in Apple Mail. Problem is, I often notice that doing so cycles through several similar signatures in a decidedly non-random way! (You can demo this by sending email to , with a search term in the Subject: line.) But of course, a nuclear plant is not going to melt down because of my signature line. So RAND() can be useful, but it is not really very random, and should be used with caution. My guess is that it's using Knuth's linear congruential algorithm that has well-known problems, but that unfortunately has been hidden deep in system code libraries since "Fundamental Algorithms" was published in 1968. World events tend to be driven by loose coalitions of economic, political, and military interests, which function like guilds of species in an ecosystem. These guilds generate patterns of events that meet the interests of these coalitions, without there being any unity of purpose or clear plan. When powerful players accept they are not all-powerful, they increase their effectiveness, but are also able to deny and cover any responsibility for the adverse outcomes of those actions. -- David Holmgren Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
The separate table for the IDs is probably best solution, maybe counting on caching of the table with an index on the id value to speed up the 'where' clause; this checks what numbers are left instead of what numbers have been used; the disadvantage is that you have to manage a second table with a million rows! You could generate a memory table when you open the session, populate it with all possible values and then delete all already assigned values. You would have to do this only once and then all possible unused values would be available. It shouldn't get slower with time (in fact it might speed up as the used rows are progressively deleted). It has the advantage that the random function is called only once: whereas using a single table requires looping until a unique random value is found, and as the table fills this will get really slow. - Original Message - From: "Jerry Schwartz" To: "'Andre Matos'" ; "'Steven Staples'" Cc: Sent: Friday, May 28, 2010 6:51 PM Subject: RE: Using RAND to get a unique ID that has not been used yet -Original Message- From: Andre Matos [mailto:andrema...@mineirinho.org] Sent: Friday, May 28, 2010 1:44 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet It seems to be a good approach, although I was trying to get this by querying the table without creating another table to keep the Ids. [JS] That would be a VERY bad idea. My predecessor designed our system that way: it would generate a random key, check to see if that key were in use, and either use it or try again. As you would expect, the whole process get slower and slower as we "ran out" of unique keys. Eventually the whole application became unusable. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Thanks, Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 12:15 PM, Steven Staples wrote: If you wanted to use/go that route, then why not select a random limit 1 from that table, and then delete that row? SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; On a side note, I would use the auto-inc field still, and store this number in another field. Steven Staples -Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: May 28, 2010 11:49 AM To: Andre Matos Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos wrote: Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mai
Re: Using RAND to get a unique ID that has not been used yet
On Fri, May 28, 2010 at 11:38 AM, Andre Matos wrote: > I have a table that uses auto_increment to generate the Id automatically > working fine. > However, I need to create a new table where the Id must be a number generated > randomly, so I cannot use the auto_increment. You'd be better off using UUID in my opinion. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
When I mentioned having everything in the Query, I was thinking about this. I don't want to have a loop repeating the query until I get a unique Id. This is ridicules and imagine how many queries I might end up running. No way! Thanks for the warning and feedback! Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 1:51 PM, Jerry Schwartz wrote: > >> -Original Message- >> From: Andre Matos [mailto:andrema...@mineirinho.org] >> Sent: Friday, May 28, 2010 1:44 PM >> To: Steven Staples >> Cc: mysql@lists.mysql.com >> Subject: Re: Using RAND to get a unique ID that has not been used yet >> >> It seems to be a good approach, although I was trying to get this by querying >> the table without creating another table to keep the Ids. >> > [JS] That would be a VERY bad idea. My predecessor designed our system that > way: it would generate a random key, check to see if that key were in use, > and > either use it or try again. > > As you would expect, the whole process get slower and slower as we "ran out" > of unique keys. Eventually the whole application became unusable. > > Regards, > > Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > www.the-infoshop.com > > > > >> Thanks, >> >> Andre >> >> -- >> Andre Matos >> andrema...@mineirinho.org >> >> >> >> >> On 2010-05-28, at 12:15 PM, Steven Staples wrote: >> >>> If you wanted to use/go that route, then why not select a random limit 1 >>> from that table, and then delete that row? >>> >>> SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; >>> >>> >>> On a side note, I would use the auto-inc field still, and store this number >>> in another field. >>> >>> Steven Staples >>> >>> >>> >>>> -Original Message- >>>> From: Jim Lyons [mailto:jlyons4...@gmail.com] >>>> Sent: May 28, 2010 11:49 AM >>>> To: Andre Matos >>>> Cc: mysql@lists.mysql.com >>>> Subject: Re: Using RAND to get a unique ID that has not been used yet >>>> >>>> If your specs are that specific (IDs must be between 1 and 99) >>>> then you could create a 99-row table with one integer column and >>>> prefill it with the numbers 1 to 99 in random order. >>>> >>>> Then you could write a function that would select and return the first >>>> number in the table, then delete that record so you would not reuse >>>> it. >>>> >>>> Once you've done the work of sorting 99 numbers in random order >>>> (which can be done anywhich way) it's easy and you don't have to loop >>>> an indeterminant number of times. You would be looping an increasing >>>> number of times as you begin to fill up the table. >>>> >>>> Jim >>>> >>>> On Fri, May 28, 2010 at 10:38 AM, Andre Matos >>>> wrote: >>>>> Hi All, >>>>> >>>>> I have a table that uses auto_increment to generate the Id automatically >>>> working fine. However, I need to create a new table where the Id must be a >>>> number generated randomly, so I cannot use the auto_increment. >>>>> >>>>> MySQL has a function RAND. So I could use something like this: >>>>> >>>>> SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable >>>>> >>>>> But, let's suppose that the RandId is a number that was already used in >>>> the table. Then I need to run the SELECT again and again until I find a >>>> number that hasn't been used. >>>>> >>>>> Is there a way to have this SELECT to loop until it finds a number that >>>> hasn't been used? >>>>> >>>>> The RandId must be only numbers and length of 6 (from 1 to 99). No >>>> other character is allowed. >>>>> >>>>> Thanks for any help! >>>>> >>>>> Andre >>>>> >>>>> -- >>>>> Andre Matos >>>>> andrema...@mineirinho.org >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> MySQL General Mailing List >>>>> For list archives: http://lis
RE: Using RAND to get a unique ID that has not been used yet
>-Original Message- >From: Andre Matos [mailto:andrema...@mineirinho.org] >Sent: Friday, May 28, 2010 1:44 PM >To: Steven Staples >Cc: mysql@lists.mysql.com >Subject: Re: Using RAND to get a unique ID that has not been used yet > >It seems to be a good approach, although I was trying to get this by querying >the table without creating another table to keep the Ids. > [JS] That would be a VERY bad idea. My predecessor designed our system that way: it would generate a random key, check to see if that key were in use, and either use it or try again. As you would expect, the whole process get slower and slower as we "ran out" of unique keys. Eventually the whole application became unusable. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com >Thanks, > >Andre > >-- >Andre Matos >andrema...@mineirinho.org > > > > >On 2010-05-28, at 12:15 PM, Steven Staples wrote: > >> If you wanted to use/go that route, then why not select a random limit 1 >> from that table, and then delete that row? >> >> SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; >> >> >> On a side note, I would use the auto-inc field still, and store this number >> in another field. >> >> Steven Staples >> >> >> >>> -Original Message- >>> From: Jim Lyons [mailto:jlyons4...@gmail.com] >>> Sent: May 28, 2010 11:49 AM >>> To: Andre Matos >>> Cc: mysql@lists.mysql.com >>> Subject: Re: Using RAND to get a unique ID that has not been used yet >>> >>> If your specs are that specific (IDs must be between 1 and 99) >>> then you could create a 99-row table with one integer column and >>> prefill it with the numbers 1 to 99 in random order. >>> >>> Then you could write a function that would select and return the first >>> number in the table, then delete that record so you would not reuse >>> it. >>> >>> Once you've done the work of sorting 99 numbers in random order >>> (which can be done anywhich way) it's easy and you don't have to loop >>> an indeterminant number of times. You would be looping an increasing >>> number of times as you begin to fill up the table. >>> >>> Jim >>> >>> On Fri, May 28, 2010 at 10:38 AM, Andre Matos >>> wrote: >>>> Hi All, >>>> >>>> I have a table that uses auto_increment to generate the Id automatically >>> working fine. However, I need to create a new table where the Id must be a >>> number generated randomly, so I cannot use the auto_increment. >>>> >>>> MySQL has a function RAND. So I could use something like this: >>>> >>>> SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable >>>> >>>> But, let's suppose that the RandId is a number that was already used in >>> the table. Then I need to run the SELECT again and again until I find a >>> number that hasn't been used. >>>> >>>> Is there a way to have this SELECT to loop until it finds a number that >>> hasn't been used? >>>> >>>> The RandId must be only numbers and length of 6 (from 1 to 99). No >>> other character is allowed. >>>> >>>> Thanks for any help! >>>> >>>> Andre >>>> >>>> -- >>>> Andre Matos >>>> andrema...@mineirinho.org >>>> >>>> >>>> >>>> >>>> >>>> -- >>>> MySQL General Mailing List >>>> For list archives: http://lists.mysql.com/mysql >>>> To unsubscribe: >>> http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com >>>> >>>> >>> >>> >>> >>> -- >>> Jim Lyons >>> Web developer / Database administrator >>> http://www.weblyons.com >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net >>> >>> No virus found in this incoming message. >>> Checked by AVG - www.avg.com >>> Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 >>> 02:25:00 >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org >> > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Using RAND to get a unique ID that has not been used yet
>-Original Message- >From: Jim Lyons [mailto:jlyons4...@gmail.com] >Sent: Friday, May 28, 2010 11:49 AM >To: Andre Matos >Cc: mysql@lists.mysql.com >Subject: Re: Using RAND to get a unique ID that has not been used yet > >If your specs are that specific (IDs must be between 1 and 99) >then you could create a 99-row table with one integer column and >prefill it with the numbers 1 to 99 in random order. > >Then you could write a function that would select and return the first >number in the table, then delete that record so you would not reuse >it. > >Once you've done the work of sorting 99 numbers in random order >(which can be done anywhich way) it's easy and you don't have to loop >an indeterminant number of times. You would be looping an increasing >number of times as you begin to fill up the table. > [JS] You don't have to go to the trouble of sorting the "number" table in any order, random or anything else. Just select a random record from that table. Since you'll have the number of that record, you can delete it. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
It seems to be a good approach, although I was trying to get this by querying the table without creating another table to keep the Ids. Thanks, Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 12:15 PM, Steven Staples wrote: > If you wanted to use/go that route, then why not select a random limit 1 > from that table, and then delete that row? > > SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; > > > On a side note, I would use the auto-inc field still, and store this number > in another field. > > Steven Staples > > > >> -Original Message- >> From: Jim Lyons [mailto:jlyons4...@gmail.com] >> Sent: May 28, 2010 11:49 AM >> To: Andre Matos >> Cc: mysql@lists.mysql.com >> Subject: Re: Using RAND to get a unique ID that has not been used yet >> >> If your specs are that specific (IDs must be between 1 and 99) >> then you could create a 99-row table with one integer column and >> prefill it with the numbers 1 to 99 in random order. >> >> Then you could write a function that would select and return the first >> number in the table, then delete that record so you would not reuse >> it. >> >> Once you've done the work of sorting 99 numbers in random order >> (which can be done anywhich way) it's easy and you don't have to loop >> an indeterminant number of times. You would be looping an increasing >> number of times as you begin to fill up the table. >> >> Jim >> >> On Fri, May 28, 2010 at 10:38 AM, Andre Matos >> wrote: >>> Hi All, >>> >>> I have a table that uses auto_increment to generate the Id automatically >> working fine. However, I need to create a new table where the Id must be a >> number generated randomly, so I cannot use the auto_increment. >>> >>> MySQL has a function RAND. So I could use something like this: >>> >>> SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable >>> >>> But, let's suppose that the RandId is a number that was already used in >> the table. Then I need to run the SELECT again and again until I find a >> number that hasn't been used. >>> >>> Is there a way to have this SELECT to loop until it finds a number that >> hasn't been used? >>> >>> The RandId must be only numbers and length of 6 (from 1 to 99). No >> other character is allowed. >>> >>> Thanks for any help! >>> >>> Andre >>> >>> -- >>> Andre Matos >>> andrema...@mineirinho.org >>> >>> >>> >>> >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com >>> >>> >> >> >> >> -- >> Jim Lyons >> Web developer / Database administrator >> http://www.weblyons.com >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net >> >> No virus found in this incoming message. >> Checked by AVG - www.avg.com >> Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 >> 02:25:00 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Using RAND to get a unique ID that has not been used yet
If you wanted to use/go that route, then why not select a random limit 1 from that table, and then delete that row? SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; On a side note, I would use the auto-inc field still, and store this number in another field. Steven Staples > -Original Message- > From: Jim Lyons [mailto:jlyons4...@gmail.com] > Sent: May 28, 2010 11:49 AM > To: Andre Matos > Cc: mysql@lists.mysql.com > Subject: Re: Using RAND to get a unique ID that has not been used yet > > If your specs are that specific (IDs must be between 1 and 99) > then you could create a 99-row table with one integer column and > prefill it with the numbers 1 to 99 in random order. > > Then you could write a function that would select and return the first > number in the table, then delete that record so you would not reuse > it. > > Once you've done the work of sorting 99 numbers in random order > (which can be done anywhich way) it's easy and you don't have to loop > an indeterminant number of times. You would be looping an increasing > number of times as you begin to fill up the table. > > Jim > > On Fri, May 28, 2010 at 10:38 AM, Andre Matos > wrote: > > Hi All, > > > > I have a table that uses auto_increment to generate the Id automatically > working fine. However, I need to create a new table where the Id must be a > number generated randomly, so I cannot use the auto_increment. > > > > MySQL has a function RAND. So I could use something like this: > > > > SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable > > > > But, let's suppose that the RandId is a number that was already used in > the table. Then I need to run the SELECT again and again until I find a > number that hasn't been used. > > > > Is there a way to have this SELECT to loop until it finds a number that > hasn't been used? > > > > The RandId must be only numbers and length of 6 (from 1 to 99). No > other character is allowed. > > > > Thanks for any help! > > > > Andre > > > > -- > > Andre Matos > > andrema...@mineirinho.org > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com > > > > > > > > -- > Jim Lyons > Web developer / Database administrator > http://www.weblyons.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 > 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos wrote: > Hi All, > > I have a table that uses auto_increment to generate the Id automatically > working fine. However, I need to create a new table where the Id must be a > number generated randomly, so I cannot use the auto_increment. > > MySQL has a function RAND. So I could use something like this: > > SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable > > But, let's suppose that the RandId is a number that was already used in the > table. Then I need to run the SELECT again and again until I find a number > that hasn't been used. > > Is there a way to have this SELECT to loop until it finds a number that > hasn't been used? > > The RandId must be only numbers and length of 6 (from 1 to 99). No other > character is allowed. > > Thanks for any help! > > Andre > > -- > Andre Matos > andrema...@mineirinho.org > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com > > -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using RAND to get a unique ID that has not been used yet
Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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]
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
> SELECT storage_path, image_md5, id > FROM images > JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved = > 1)) AS id) AS r2 USING (id) >WHERE approved = 1; > > I really do not get this, SELECT CEIL(RAND() will always return 1 will it > not? Any idea why I get an empty result set at times? http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand So CEIL(RAND() ) would always be 1. My guess is you have is that you have hole(s) in your data set. For the record you are not doing CEIL(RAND() ), your doing CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved => 1)) -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Age old order by rand() issue
Hi, Responses inline On Thu, May 1, 2008 at 3:11 PM, Scott Haneda <[EMAIL PROTECTED]> wrote: > List search seems to return 0 results, and I am a bit stumped. > > Getting a more optimized order by random with 1 record... > I found a snip online that works, but seems to return an empty on occasion, > and I am not sure why: > > SELECT storage_path, image_md5, id > FROM images > JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved = > 1)) AS id) AS r2 USING (id) >WHERE approved = 1; > > I really do not get this, SELECT CEIL(RAND() will always return 1 will it > not? Any idea why I get an empty result set at times? http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand So CEIL(RAND() ) would always be 1. My guess is you have is that you have hole(s) in your data set. > > I then managed to rig this together: > SELECT * FROM images AS t > JOIN (SELECT CEIL(MAX(id)*RAND()) AS id > FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; > > This works, but I get an odd result, in that the id column is listed twice, > once at the beginning, where it is in the table, and once at the end. > Duplicate values of course. Using a star is less than a great idea. You are a second id from CEIL(MAX(id)*RAND()) AS id being joined in. Specify the columns you want... > If I narrow the * to a real called select, such as > SELECT id, storage_path, image_md5 FROM images AS t > JOIN (SELECT CEIL(MAX(id)*RAND()) AS id > FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; > > -> FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; > ERROR 1052 (23000): Column 'id' in field list is ambiguous Mysql wants you to specify what table you want the id from. Meaning from x or t... SELECT t.id, storage_path, image_md5 FROM images AS t JOIN (SELECT CEIL(MAX(id)*RAND()) AS id FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; I have seen nicer fast random row implement, but that will work. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Age old order by rand() issue
List search seems to return 0 results, and I am a bit stumped. Getting a more optimized order by random with 1 record... I found a snip online that works, but seems to return an empty on occasion, and I am not sure why: SELECT storage_path, image_md5, id FROM images JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved = 1)) AS id) AS r2 USING (id) WHERE approved = 1; I really do not get this, SELECT CEIL(RAND() will always return 1 will it not? Any idea why I get an empty result set at times? I then managed to rig this together: SELECT * FROM images AS t JOIN (SELECT CEIL(MAX(id)*RAND()) AS id FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; This works, but I get an odd result, in that the id column is listed twice, once at the beginning, where it is in the table, and once at the end. Duplicate values of course. If I narrow the * to a real called select, such as SELECT id, storage_path, image_md5 FROM images AS t JOIN (SELECT CEIL(MAX(id)*RAND()) AS id FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; -> FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; ERROR 1052 (23000): Column 'id' in field list is ambiguous I can not seem to get past that error, and would like to call just the columns I want. Or, if someone has a better random record return select, I am all ears. Thanks so much. -- Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
implementation of SELECT ... ORDER BY RAND() LIMIT 1
2007/2/7, Jos Elkink <[EMAIL PROTECTED]>: Hi all, I have a question about the combination of RAND and LIMIT 1. If I have a query like: SELECT ... ORDER BY RAND() LIMIT 1 with the ... replaced with a normal query on one table. How is this implemented? Is this optimized for the fact that it only needs one entry? And what about when there is a combination of tables SELECT a.a, b.b FROM a,b WHERE a.b = b.id ORDER BY RAND() LIMIT 1 And in the case of SELECT a.a, b.b FROM a LEFT JOIN b ON a.b = b.id ORDER BY RAND() LIMIT 1 Some say that especially in the last two cases, it is faster to just retrieve the entire list and then select randomly. And what if the case is that the limit is larger than 1, but smaller than the entire table? I am asking because we have various of these queries in our code and serious issues with speed, and I was wondering whether I am assuming optimization in the mysql code where they don't actually exist. Any help on this would be much appreciated. I just dealt with this problem myself. The problem as far as I understand it is that ORDER BY RAND() LIMIT 1 does a full table scan. I found the solution and a serious speedup in the comments on this page: http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ HTH, Jan Pieter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: implementation of SELECT ... ORDER BY RAND() LIMIT 1
I have a question about the combination of RAND and LIMIT 1. If I have a query like: SELECT ... ORDER BY RAND() LIMIT 1 with the ... replaced with a normal query on one table. How is this implemented? Is this optimized for the fact that it only needs one entry? Try prefixing your query with "EXPLAIN" and see what it says it's going to do. Pretty sure it's going to look at *every* row in the table, compute a random value, sort it, then return the first one. So, for a table with a good number of rows, the above is going to be horrificly inefficient. It would be a lot faster to do something like: rowcount = select count(*) from table random_value = something between 0 and rowcount - 1 select ... LIMIT 1 OFFSET random_value -philip And what about when there is a combination of tables SELECT a.a, b.b FROM a,b WHERE a.b = b.id ORDER BY RAND() LIMIT 1 And in the case of SELECT a.a, b.b FROM a LEFT JOIN b ON a.b = b.id ORDER BY RAND() LIMIT 1 Some say that especially in the last two cases, it is faster to just retrieve the entire list and then select randomly. And what if the case is that the limit is larger than 1, but smaller than the entire table? I am asking because we have various of these queries in our code and serious issues with speed, and I was wondering whether I am assuming optimization in the mysql code where they don't actually exist. Any help on this would be much appreciated. Regards, Jos http://www.cantr.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
implementation of SELECT ... ORDER BY RAND() LIMIT 1
Hi all, I have a question about the combination of RAND and LIMIT 1. If I have a query like: SELECT ... ORDER BY RAND() LIMIT 1 with the ... replaced with a normal query on one table. How is this implemented? Is this optimized for the fact that it only needs one entry? And what about when there is a combination of tables SELECT a.a, b.b FROM a,b WHERE a.b = b.id ORDER BY RAND() LIMIT 1 And in the case of SELECT a.a, b.b FROM a LEFT JOIN b ON a.b = b.id ORDER BY RAND() LIMIT 1 Some say that especially in the last two cases, it is faster to just retrieve the entire list and then select randomly. And what if the case is that the limit is larger than 1, but smaller than the entire table? I am asking because we have various of these queries in our code and serious issues with speed, and I was wondering whether I am assuming optimization in the mysql code where they don't actually exist. Any help on this would be much appreciated. Regards, Jos http://www.cantr.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER BY RAND() gives me duplicate rows sometimes
The SELECT that Daevid originally tried is straight out of the documentation, which says that ORDER BY RAND() LIMIT x is a good way to get a random sample of your data. The documentation also says you can't use a RAND() column in an ORDER BY clause because the ORDER BY would evaluate the column multiple times. I'm not sure what, exactly, the difference is between the two. In any case, as I understand it your (Christian's) suggestion runs counter to the documentation. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Christian Hammers [mailto:[EMAIL PROTECTED] > Sent: Friday, November 10, 2006 2:57 AM > To: Daevid Vincent > Cc: mysql@lists.mysql.com > Subject: Re: ORDER BY RAND() gives me duplicate rows sometimes > > > > On 2006-11-09 Daevid Vincent wrote: > > I am using this query to pull three random comments from a table: > > > > "SELECT *, DATE_FORMAT(created_on, '%b %D') as date_format > FROM comments > > ORDER BY RAND() LIMIT 3"; > > > > The problem is that sometimes, I get two of the same > comment. How can I > > refine this query to give me 3 unique/distinct ones? > > Maybe > > SELECT DISTINCT > rand() as rnd, > *, > DATE_FORMAT(created_on, '%b %D') as date_format > FROM > comments > ORDER BY > rnd > LIMIT > 3 > ; > > bye, > > -christian- > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY RAND() gives me duplicate rows sometimes
Add DISTINCT(primary_key) in your query? Regards Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY RAND() gives me duplicate rows sometimes
On 2006-11-09 Daevid Vincent wrote: > I am using this query to pull three random comments from a table: > > "SELECT *, DATE_FORMAT(created_on, '%b %D') as date_format FROM comments > ORDER BY RAND() LIMIT 3"; > > The problem is that sometimes, I get two of the same comment. How can I > refine this query to give me 3 unique/distinct ones? Maybe SELECT DISTINCT rand() as rnd, *, DATE_FORMAT(created_on, '%b %D') as date_format FROM comments ORDER BY rnd LIMIT 3 ; bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY RAND() gives me duplicate rows sometimes
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: rand()
At 11:25 AM 7/7/2006, you wrote: I think you meant ORDER BY `registers`.. He may have meant that but then you lose the randomness. I think it has to be done in 2 steps. I don't see any way of doing it without creating a temporary table. The SQL might look something like this: drop temporary table if exists tmpreg; create temporary table tmpreg select registers from tablex order by rand() limit 10; select * from temporary tmpreg order by registers; Mike Jay Blanchard пиÑеÑ: [snip] I´ve got a page where a ought to get 20 registers in ramdom order but i want to display it in an alphabetical order. Someone knows if there is a way to get that 20 random registers in alphabetical order? [/snip] SORT BY `registers` -- 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: rand()
Hi Jay, thanks a lot for your help. I tried it and it don´t work, but i tried other way that works fine. I´ll put it here because it could help other people. select * from ( select g.grade_id as grade_id, concat(p.nome," ",g.grade_subtitulo) as nome from grade g inner join produto p on g.produto_id=p.id where (select count(*) from produtos_lista pl where pl.grade_id=g.grade_id)=0 order by rand() limit 20) as t1 order by t1.nome Thanks again. ""João Cândido de Souza Neto"" <[EMAIL PROTECTED]> escreveu na mensagem news:[EMAIL PROTECTED] > Hi everyone, > > I´ve got a page where a ought to get 20 registers in ramdom order but i > want to display it in an alphabetical order. > > Someone knows if there is a way to get that 20 random registers in > alphabetical order? > > Thanks. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rand()
I think you meant ORDER BY `registers`.. Jay Blanchard пишет: [snip] I´ve got a page where a ought to get 20 registers in ramdom order but i want to display it in an alphabetical order. Someone knows if there is a way to get that 20 random registers in alphabetical order? [/snip] SORT BY `registers` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rand()
[snip] Excuse me, but i don´t understand your answer. Could you explain it? [/snip] Add it to the end of your query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rand()
Excuse me, but i don´t understand your answer. Could you explain it? ""Jay Blanchard"" <[EMAIL PROTECTED]> escreveu na mensagem news:[EMAIL PROTECTED] [snip] I´ve got a page where a ought to get 20 registers in ramdom order but i want to display it in an alphabetical order. Someone knows if there is a way to get that 20 random registers in alphabetical order? [/snip] SORT BY `registers` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rand()
[snip] I´ve got a page where a ought to get 20 registers in ramdom order but i want to display it in an alphabetical order. Someone knows if there is a way to get that 20 random registers in alphabetical order? [/snip] SORT BY `registers` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rand()
Hi everyone, I´ve got a page where a ought to get 20 registers in ramdom order but i want to display it in an alphabetical order. Someone knows if there is a way to get that 20 random registers in alphabetical order? Thanks. -- 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]
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]
change the seed on the random function. http://dev.mysql.com/doc/mysql/en/mathematical-functions.html On Mon, 2005-10-03 at 08:33 -0700, Graham Anderson wrote: > is there a way to make ORDER BY RAND() at bit more spontaneous ? > > I am using it in a php statement: > $sql = "SELECT media.id, > artist.name as artist, > artist.spanish as bio, > artist.purchaseLink, > artist.picture, > media.spanish as trackName, > media.path, > media.quality, > mediaType.id as mediaType > FROM artist, media, playlistItems, mediaType > WHERE playlistItems.playlist_id = $myID > AND playlistItems.media_id = media.id > AND media.artist_id = artist.id > AND media.mediaType_id = mediaType.id > ORDER BY RAND() LIMIT 0, 30"; > > > the result seems to be pretty predictable > is there a way to improve RAND() or is there something better ? > Should I be using php to randomize the found set instead ? > > > many thanks > g > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Making ORDER BY RAND() more random [resend]
is there a way to make ORDER BY RAND() at bit more spontaneous ? I am using it in a php statement: $sql = "SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.picture, media.spanish as trackName, media.path, media.quality, mediaType.id as mediaType FROM artist, media, playlistItems, mediaType WHERE playlistItems.playlist_id = $myID AND playlistItems.media_id = media.id AND media.artist_id = artist.id AND media.mediaType_id = mediaType.id ORDER BY RAND() LIMIT 0, 30"; the result seems to be pretty predictable is there a way to improve RAND() or is there something better ? Should I be using php to randomize the found set instead ? many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Making ORDER BY RAND() more random
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]
Re: join subquerie rand problem
thats exactly what i wanted - the perfekt solution, now i have to thing about performance but thanks to Shawn Green for trying to help :> ""Arnaud"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > > i have lets say 1000 different fruits > > and 1000 different animals > > with many to man relations > > now i want to extract 100 differnt fruits held by 100 different > > animals without dupes of fruit or animal > > That's a nice one ! I'll give it a try : > The point is to get 100 random couples of (id_fruits, id_animals), > with unique id_fruits and unique id_animals, right ? > > SELECT id_fruits AS my_id_fruits, > (SELECT id_animals > FROM fruits_animals > WHERE id_fruits = my_id_fruits > ORDER BY RAND() > LIMIT 1) AS my_id_animals > FROM fruits_animals > GROUP BY my_id_fruits > ORDER BY RAND() > LIMIT 100; > > You have your unique many-to-many relations' table, you just have to > join this with the animals and fruits tables. > > Regards, > Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join subquerie rand problem
Oops, this was not correct, excuse me ! You can have duplicate id_animals with this query. You can do it like this : SELECT my_id_fruits, my_id_animals FROM (SELECT id_fruits AS my_id_fruits, (SELECT id_animals FROM fruits_animals WHERE id_fruits = my_id_fruits ORDER BY RAND() LIMIT 1) AS my_id_animals FROM fruits_animals GROUP BY my_id_fruits) AS tmpQuery GROUP BY tmpQuery.my_id_animals ORDER BY whatever you want LIMIT 100; On 20 Jul 2004 at 16:36, Arnaud <[EMAIL PROTECTED]> wrote: > > i have lets say 1000 different fruits > > and 1000 different animals > > with many to man relations > > now i want to extract 100 differnt fruits held by 100 different > > animals without dupes of fruit or animal > > That's a nice one ! I'll give it a try : > The point is to get 100 random couples of (id_fruits, id_animals), > with unique id_fruits and unique id_animals, right ? > > SELECT id_fruits AS my_id_fruits, > (SELECT id_animals > FROM fruits_animals > WHERE id_fruits = my_id_fruits > ORDER BY RAND() > LIMIT 1) AS my_id_animals > FROM fruits_animals > GROUP BY my_id_fruits > ORDER BY RAND() > LIMIT 100; > > You have your unique many-to-many relations' table, you just have to > join this with the animals and fruits tables. > > Regards, > Arnaud > > -- > 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: join subquerie rand problem
> i have lets say 1000 different fruits > and 1000 different animals > with many to man relations > now i want to extract 100 differnt fruits held by 100 different > animals without dupes of fruit or animal That's a nice one ! I'll give it a try : The point is to get 100 random couples of (id_fruits, id_animals), with unique id_fruits and unique id_animals, right ? SELECT id_fruits AS my_id_fruits, (SELECT id_animals FROM fruits_animals WHERE id_fruits = my_id_fruits ORDER BY RAND() LIMIT 1) AS my_id_animals FROM fruits_animals GROUP BY my_id_fruits ORDER BY RAND() LIMIT 100; You have your unique many-to-many relations' table, you just have to join this with the animals and fruits tables. Regards, Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join subquerie rand problem
brings the same result as select * from fruit, fruit_animal, animal where fruit.id = fruit_animal.id_fruit AND fruit_animal.id_animal = animal.id order by rand() or i got something wrong the next thing is that the tables are hughe, like 3 millionen rows (growing) thanks btw :O <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > OK, This is a similar solution to a problem posted last month (he was > trying to match debits to credits). Here's how it works: > > Create a temporary table to match your fruits_animals table except you > want to put UNIQUE INDEXES on both columns individually. Then you run an > INSERT IGNORE to copy the rows from fruits_animals into your temp table. > What you will have when the INSERT IGNORE completes is a list that > contains all of your animals listed only once and all of the fruits listed > only once but only if that animal/fruit combination already existed. > > CREATE TEMPORARY TABLE tmpDedupe( > animal_id int not null, > fruit_id int not null, > UNIQUE INDEX (animal_id), > UNIQUE INDEX (fruit_id) > ) > > INSERT IGNORE tmpDedupe (animal_id, fruit_id) > SELECT (id_fruits, id_animals) > FROM fruits_animals > > SELECT * > FROM tmpDedupe > ORDER BY RAND() > LIMIT 100 > > DROP TABLE tmpDedupe > > Make sense? > > Yours, > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > "Gerske, Sebastian" <[EMAIL PROTECTED]> wrote on 07/20/2004 09:22:30 AM: > > > well neither a,b or c :P > > > > i have lets say 1000 different fruits > > and 1000 different animals > > with many to man relations > > now i want to extract 100 differnt fruits held by 100 different animals > > without dupes of fruit or animal > > > > > > <[EMAIL PROTECTED]> schrieb im Newsbeitrag > > > news:[EMAIL PROTECTED] > > > Sebastian, > > > > > > I don't think we completely understand your questionor someone > would > > > have responded long before now. Let me see if I can rephrase the > > > situation and maybe we can get a response. > > > > > > You have two tables that contain objects (your example: fruits and > > > animals) and a table that relates them (example: fruits_animals) in a > > > many-to-many relationship. > > > > > > Is your situation: > > > A) You want to return 100 un-duplicated random combinations of the > > > objects, regardless of if they have an entry in the relationship > table? > > > - OR - > > > B) You want a list of 100 un-duplicated random rows from the > relationship > > > table? > > > - OR - > > > C) Each time you run your query containing "ORDER BY RAND()" , you get > the > > > exact same set of records back? > > > > > > Yours, > > > Shawn Green > > > Database Administrator > > > Unimin Corporation - Spruce Pine > > > > > > > > > "Gerske, Sebastian" <[EMAIL PROTECTED]> wrote on 07/20/2004 06:18:11 > AM: > > > > > > > help me please :/ > > > > > > > > > > > > ""Gerske, Sebastian"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > > > > news:[EMAIL PROTECTED] > > > > > Hello list, > > > > > > > > > > i still need a solution for my 3 table join rand problem, im using > > > version > > > > > 4.1 now so subqueries are possible: > > > > > > > > > > explanation: > > > > > > > > > > i have 3 tables lets say: > > > > > > > > > > fruits: > > > > > > > > > > id, name > > > > > 1banana > > > > > 2apple > > > > > 3strawberry > > > > > > > > > > fruits_animals > > > > > > > > > > id, id_fruits, id_animals > > > > > 11 2 > > > > > 21 3 > > > > > 33 1 > > > > > 42 4 > > > > > > > > > > animals > > > > > > > > > > id, name > > > > > 1cat > > > > > 2 dog > > > > > 3 mouse > > > > > 4 elephant > > > > > > > > > > > > > > > My problem is that if i join the tables and order them by rand i > > > always > > > > get > > > > > one result something like: > > > > > strawberry, cat (fruit id = 3, animal id = 1 ) > > > > > or > > > > > banana, dog (fruit id = 1, animal id = 2) > > > > > but never > > > > > banana, mouse( fruit id = 1, animal id = 3 ) > > > > > > > > > > and need to select 100 different relations without redundancies > hows > > > that > > > > > possible ? > > > > > > > > > > regards sebastian gerske > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > 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: join subquerie rand problem
OK, This is a similar solution to a problem posted last month (he was trying to match debits to credits). Here's how it works: Create a temporary table to match your fruits_animals table except you want to put UNIQUE INDEXES on both columns individually. Then you run an INSERT IGNORE to copy the rows from fruits_animals into your temp table. What you will have when the INSERT IGNORE completes is a list that contains all of your animals listed only once and all of the fruits listed only once but only if that animal/fruit combination already existed. CREATE TEMPORARY TABLE tmpDedupe( animal_id int not null, fruit_id int not null, UNIQUE INDEX (animal_id), UNIQUE INDEX (fruit_id) ) INSERT IGNORE tmpDedupe (animal_id, fruit_id) SELECT (id_fruits, id_animals) FROM fruits_animals SELECT * FROM tmpDedupe ORDER BY RAND() LIMIT 100 DROP TABLE tmpDedupe Make sense? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Gerske, Sebastian" <[EMAIL PROTECTED]> wrote on 07/20/2004 09:22:30 AM: > well neither a,b or c :P > > i have lets say 1000 different fruits > and 1000 different animals > with many to man relations > now i want to extract 100 differnt fruits held by 100 different animals > without dupes of fruit or animal > > > <[EMAIL PROTECTED]> schrieb im Newsbeitrag > news:[EMAIL PROTECTED] > > Sebastian, > > > > I don't think we completely understand your questionor someone would > > have responded long before now. Let me see if I can rephrase the > > situation and maybe we can get a response. > > > > You have two tables that contain objects (your example: fruits and > > animals) and a table that relates them (example: fruits_animals) in a > > many-to-many relationship. > > > > Is your situation: > > A) You want to return 100 un-duplicated random combinations of the > > objects, regardless of if they have an entry in the relationship table? > > - OR - > > B) You want a list of 100 un-duplicated random rows from the relationship > > table? > > - OR - > > C) Each time you run your query containing "ORDER BY RAND()" , you get the > > exact same set of records back? > > > > Yours, > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > "Gerske, Sebastian" <[EMAIL PROTECTED]> wrote on 07/20/2004 06:18:11 AM: > > > > > help me please :/ > > > > > > > > > ""Gerske, Sebastian"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > > > news:[EMAIL PROTECTED] > > > > Hello list, > > > > > > > > i still need a solution for my 3 table join rand problem, im using > > version > > > > 4.1 now so subqueries are possible: > > > > > > > > explanation: > > > > > > > > i have 3 tables lets say: > > > > > > > > fruits: > > > > > > > > id, name > > > > 1banana > > > > 2apple > > > > 3strawberry > > > > > > > > fruits_animals > > > > > > > > id, id_fruits, id_animals > > > > 11 2 > > > > 21 3 > > > > 33 1 > > > > 42 4 > > > > > > > > animals > > > > > > > > id, name > > > > 1cat > > > > 2 dog > > > > 3 mouse > > > > 4 elephant > > > > > > > > > > > > My problem is that if i join the tables and order them by rand i > > always > > > get > > > > one result something like: > > > > strawberry, cat (fruit id = 3, animal id = 1 ) > > > > or > > > > banana, dog (fruit id = 1, animal id = 2) > > > > but never > > > > banana, mouse( fruit id = 1, animal id = 3 ) > > > > > > > > and need to select 100 different relations without redundancies hows > > that > > > > possible ? > > > > > > > > regards sebastian gerske > > > > > > > > > > > > > > > > > > > > -- > > > 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: join subquerie rand problem
well neither a,b or c :P i have lets say 1000 different fruits and 1000 different animals with many to man relations now i want to extract 100 differnt fruits held by 100 different animals without dupes of fruit or animal <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > Sebastian, > > I don't think we completely understand your questionor someone would > have responded long before now. Let me see if I can rephrase the > situation and maybe we can get a response. > > You have two tables that contain objects (your example: fruits and > animals) and a table that relates them (example: fruits_animals) in a > many-to-many relationship. > > Is your situation: > A) You want to return 100 un-duplicated random combinations of the > objects, regardless of if they have an entry in the relationship table? > - OR - > B) You want a list of 100 un-duplicated random rows from the relationship > table? > - OR - > C) Each time you run your query containing "ORDER BY RAND()" , you get the > exact same set of records back? > > Yours, > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > "Gerske, Sebastian" <[EMAIL PROTECTED]> wrote on 07/20/2004 06:18:11 AM: > > > help me please :/ > > > > > > ""Gerske, Sebastian"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > > news:[EMAIL PROTECTED] > > > Hello list, > > > > > > i still need a solution for my 3 table join rand problem, im using > version > > > 4.1 now so subqueries are possible: > > > > > > explanation: > > > > > > i have 3 tables lets say: > > > > > > fruits: > > > > > > id, name > > > 1banana > > > 2apple > > > 3strawberry > > > > > > fruits_animals > > > > > > id, id_fruits, id_animals > > > 11 2 > > > 21 3 > > > 33 1 > > > 42 4 > > > > > > animals > > > > > > id, name > > > 1cat > > > 2 dog > > > 3 mouse > > > 4 elephant > > > > > > > > > My problem is that if i join the tables and order them by rand i > always > > get > > > one result something like: > > > strawberry, cat (fruit id = 3, animal id = 1 ) > > > or > > > banana, dog (fruit id = 1, animal id = 2) > > > but never > > > banana, mouse( fruit id = 1, animal id = 3 ) > > > > > > and need to select 100 different relations without redundancies hows > that > > > possible ? > > > > > > regards sebastian gerske > > > > > > > > > > > > > > -- > > 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: join subquerie rand problem
Sebastian, I don't think we completely understand your questionor someone would have responded long before now. Let me see if I can rephrase the situation and maybe we can get a response. You have two tables that contain objects (your example: fruits and animals) and a table that relates them (example: fruits_animals) in a many-to-many relationship. Is your situation: A) You want to return 100 un-duplicated random combinations of the objects, regardless of if they have an entry in the relationship table? - OR - B) You want a list of 100 un-duplicated random rows from the relationship table? - OR - C) Each time you run your query containing "ORDER BY RAND()" , you get the exact same set of records back? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Gerske, Sebastian" <[EMAIL PROTECTED]> wrote on 07/20/2004 06:18:11 AM: > help me please :/ > > > ""Gerske, Sebastian"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > news:[EMAIL PROTECTED] > > Hello list, > > > > i still need a solution for my 3 table join rand problem, im using version > > 4.1 now so subqueries are possible: > > > > explanation: > > > > i have 3 tables lets say: > > > > fruits: > > > > id, name > > 1banana > > 2apple > > 3strawberry > > > > fruits_animals > > > > id, id_fruits, id_animals > > 11 2 > > 21 3 > > 33 1 > > 42 4 > > > > animals > > > > id, name > > 1cat > > 2 dog > > 3 mouse > > 4 elephant > > > > > > My problem is that if i join the tables and order them by rand i always > get > > one result something like: > > strawberry, cat (fruit id = 3, animal id = 1 ) > > or > > banana, dog (fruit id = 1, animal id = 2) > > but never > > banana, mouse( fruit id = 1, animal id = 3 ) > > > > and need to select 100 different relations without redundancies hows that > > possible ? > > > > regards sebastian gerske > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: join subquerie rand problem
i think its not even possible with subqueries ""Gerske, Sebastian"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > help me please :/ > > > ""Gerske, Sebastian"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > news:[EMAIL PROTECTED] > > Hello list, > > > > i still need a solution for my 3 table join rand problem, im using version > > 4.1 now so subqueries are possible: > > > > explanation: > > > > i have 3 tables lets say: > > > > fruits: > > > > id, name > > 1banana > > 2apple > > 3strawberry > > > > fruits_animals > > > > id, id_fruits, id_animals > > 11 2 > > 21 3 > > 33 1 > > 42 4 > > > > animals > > > > id, name > > 1cat > > 2 dog > > 3 mouse > > 4 elephant > > > > > > My problem is that if i join the tables and order them by rand i always > get > > one result something like: > > strawberry, cat (fruit id = 3, animal id = 1 ) > > or > > banana, dog (fruit id = 1, animal id = 2) > > but never > > banana, mouse( fruit id = 1, animal id = 3 ) > > > > and need to select 100 different relations without redundancies hows that > > possible ? > > > > regards sebastian gerske > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join subquerie rand problem
help me please :/ ""Gerske, Sebastian"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > Hello list, > > i still need a solution for my 3 table join rand problem, im using version > 4.1 now so subqueries are possible: > > explanation: > > i have 3 tables lets say: > > fruits: > > id, name > 1banana > 2apple > 3strawberry > > fruits_animals > > id, id_fruits, id_animals > 11 2 > 21 3 > 33 1 > 42 4 > > animals > > id, name > 1 cat > 2 dog > 3 mouse > 4 elephant > > > My problem is that if i join the tables and order them by rand i always get > one result something like: > strawberry, cat (fruit id = 3, animal id = 1 ) > or > banana, dog (fruit id = 1, animal id = 2) > but never > banana, mouse( fruit id = 1, animal id = 3 ) > > and need to select 100 different relations without redundancies hows that > possible ? > > regards sebastian gerske > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join subquerie rand problem
Hello list, i still need a solution for my 3 table join rand problem, im using version 4.1 now so subqueries are possible: explanation: i have 3 tables lets say: fruits: id, name 1banana 2apple 3strawberry fruits_animals id, id_fruits, id_animals 11 2 21 3 33 1 42 4 animals id, name 1cat 2 dog 3 mouse 4 elephant My problem is that if i join the tables and order them by rand i always get one result something like: strawberry, cat (fruit id = 3, animal id = 1 ) or banana, dog (fruit id = 1, animal id = 2) but never banana, mouse( fruit id = 1, animal id = 3 ) and need to select 100 different relations without redundancies hows that possible ? regards sebastian gerske -- 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]
Oder by RAND Problem
Hello list, im having a hughe problem with the RAND() function first of all im using mysql 3.23 so subquerys are impossible. 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 | - 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]
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]
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
Try seeding your rand. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 31, 2004 12:57 PM > To: [EMAIL PROTECTED] > Subject: group by & order by rand() problem > > I have this table: > > mysql> select * from banners; > ++-+---+ > | id | user_id | title | > ++-+---+ > | 1 | 1 | first banner | > | 2 | 1 | second banner | > | 3 | 2 | third banner | > | 4 | 2 | forth banner | > | 5 | 2 | fifth banner | > ++-+---+ > > I would like to show a random banner for each user, > something like this: > > first call > ++-+---+ > | id | user_id | title | > ++-+---+ > | 1 | 1 | first banner | > | 3 | 2 | third banner | > ++-+---+ > > second call > ++-+---+ > | id | user_id | title | > ++-+---+ > | 2 | 1 | second banner | > | 4 | 2 | forth banner | > ++-+---+ > > etc... > > I have tried with following query but the banner > doesn't change while multiple calls: > > SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); > > Can anyone help me? > > Thanks in advance, > > Alex > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
group by & order by rand() problem
I have this table: mysql> select * from banners; ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 2 | 1 | second banner | | 3 | 2 | third banner | | 4 | 2 | forth banner | | 5 | 2 | fifth banner | ++-+---+ I would like to show a random banner for each user, something like this: first call ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 3 | 2 | third banner | ++-+---+ second call ++-+---+ | id | user_id | title | ++-+---+ | 2 | 1 | second banner | | 4 | 2 | forth banner | ++-+---+ etc... I have tried with following query but the banner doesn't change while multiple calls: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); Can anyone help me? Thanks in advance, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY RAND() performance
Donny Simonton wrote: Neil, We never delete from primary tables. No questions asked! We would just mark a entry as deleted, and not select from it. Another option you can do to solve your deletion problem is, select 35 rows for example, when you really only want 30. That way, you can have extras, if say #20 is not available. There are many options, we have even in some cases, created a table and run the order by rand query every 5 minutes and just have it update a table. And then we just do a select from that secondary table. So every 5 minutes you have new random items. Another way is to guess a reasonably narrow fixed-width random range for a column for which you have a key, and do ORDER BY RAND() LIMIT 1 inside it. If you guess it too narrow, double it and try again until you get enough records. The key range estimation technique is also useful in a number of other situations, eg. when paging through search results. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY RAND() performance
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
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: > 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
Ray wrote: > an alternative to the order by rand() with large record sets is to > pick a random starting point "limit $randPoint, 30" don't know if > its a viable solution to your situation, but it limits you to 2 > querys (row count, fetch) rather then the 30 (fetch 1 x 30) Thanks! I did see this suggested on another forum. However when I tried it, I found that EXPLAIN wasn't very encouraging. Using this minimal table: CREATE TABLE visible_pics ( pic_id int(10) unsigned NOT NULL default '0', doc_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (pic_id), KEY doc_id (doc_id) ) TYPE=MyISAM; mysql> explain select * from visible_pics limit 1,1; +--+--+---+--+-+--+---+---+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+--+---+--+-+--+---+---+ | visible_pics | ALL | NULL | NULL |NULL | NULL | 19633 | | +--+--+---+--+-+--+---+---+ 1 row in set (0.00 sec) mysql> explain select * from visible_pics order by pic_id limit 1,1; +--+---+---+-+-+--+---+---+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+-+-+--+---+---+ | visible_pics | index | NULL | PRIMARY | 4 | NULL | 19633 | | +--+---+---+-+-+--+---+---+ 1 row in set (0.00 sec) In both cases, the number of rows which will be scanned is close to the total number of rows. I included the second EXPLAIN to see if using pic_id (the primary key) would make any difference. It actually seems to actually be faster without using the index, in my trivial tests: mysql> select * from visible_pics order by pic_id limit 1,1; +++ | pic_id | doc_id | +++ | 11669 |258 | +++ 1 row in set (0.09 sec) mysql> select * from visible_pics order by pic_id limit 10100,1; +++ | pic_id | doc_id | +++ | 11771 |258 | +++ 1 row in set (0.08 sec) mysql> select * from visible_pics limit 10100,1; +++ | pic_id | doc_id | +++ | 11750 |258 | +++ 1 row in set (0.02 sec) mysql> select * from visible_pics limit 12100,1; +++ | pic_id | doc_id | +++ | 14085 |269 | +++ 1 row in set (0.02 sec) mysql> select * from visible_pics limit 900,1; +++ | pic_id | doc_id | +++ | 1100 | 53 | +++ 1 row in set (0.01 sec) mysql> select * from visible_pics limit 18000,1; +++ | pic_id | doc_id | +++ | 20343 |387 | +++ 1 row in set (0.03 sec) mysql> mysql> select * from visible_pics order by pic_id limit 12000,1; +++ | pic_id | doc_id | +++ | 13857 |325 | +++ 1 row in set (0.10 sec) The last one was just to confirm that there wasn't some kind of disk caching going on that affected the results. The query without using the index was definitely faster. If the average query is about 0.05 second, and you do 30 of them, then that would give about 1.5 seconds for the whole thing. This is in fact worse than just doing the "ORDER BY RAND() LIMIT 30" on the same table: mysql> select * from visible_pics order by rand() limit 30; +++ | pic_id | doc_id | +++ | 4149 | 98 | | 5030 |148 | | 1911 | 69 | | 4258 |105 | | 14131 |170 | | 17047 |165 | | 12643 |319 | | 14271 |180 | | 1815 | 69 | | 12768 |260 | | 8118 |164 | | 2339 | 87 | | 3058 | 63 | | 2573 | 46 | | 11511 |230 | | 16939 |335 | | 7749 |113 | | 6921 |164 | | 2106 | 79 | | 3609 | 91 | | 12513 |259 | | 18169 |234 | | 19173 |372 | | 11912 |305 | | 2026 | 69 | | 7697 |222 | | 20834 |447 | |977 | 53 | | 1638 | 24 | | 13986 |308 | +++ 30 rows in set (0.22 sec) This isn't as simple as it appears at first, however - this is merely the query to get 30 random pic_id's. I then have to do 30 more queries to get the "real" records in the separate table, so that I can build the HTML page with filenames, captions etc. Thanks again - this is a good one to know about (for anyone else out there who is encountering the same issues). But the above tests were on a very minimal table, with no "where" clause, because the table was specially prepared to only contain the relevant records in the first place. I am stil
RE: ORDER BY RAND() performance
Exactly, it's faster for us to pull all of the data and then randomize it locally. We have benchmarked it both ways and the local randomize was faster. Now that's if you want multiple records returned. Now if you only want one record, what we do, is create a random number, and then just do a limit 19345, 1 or something like that. We have tried another option which we stopped using which was creating 30 random numbers and then doing 30 select statements. But that was slower overall than 1 select with order by rand. One other option that we use sometimes is say you need 30 results randomized, and you have an auto-increment in your table. Create 30 random numbers, then do a select with something like this: Select * from blabla where lkajsdlkjas IN (10, 43, 22, 8981, etc...) This works fairly well, but then again, I haven't benchmarked it in a while and don't really remember how well it works. Actually, I just tried this on a table with 43 million entries and it took 0.0004 seconds. Just some ideas. Donny > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Monday, March 08, 2004 2:36 PM > To: Donny Simonton > Cc: 'Neil Gunton'; 'MySQL' > Subject: RE: ORDER BY RAND() performance > > > Donny, what do you do? Throw all the values into an array or something > on the client side, and use a random number generator to pull out the > array elements? > > I suppose (depending on resultset size) pulling that many rows from server > to client and handing on client side could be faster... > > > > > On Mon, 8 Mar 2004, Donny Simonton wrote: > > > ORDER BY RAND(), just sucks in my opinion. We have created our own > internal > > randomization system because pretty much everytime you use it will show > up > > in the slow query log, because of the using temporary, using filesort it > > does. Splitting your data into a hundred tables will still make it > "using > > temporary, using filesort". > > > > I just did a little test, where I only had 5 entries in a table, and I > using > > temp using filesort. > > > > Will it ever be improved? Probably the same time order by DESC is > improved. > > > > Donny > > > > > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > > Sent: Monday, March 08, 2004 2:05 PM > > > To: Neil Gunton > > > Cc: MySQL > > > Subject: Re: ORDER BY RAND() performance > > > > > > > > > If your infact (sounds like) storing the pictures meta-data (name, > size, > > > owner, etc) and the data (blob of some kind) .. I would definately > break > > > up the design into 2 tables. That way when dealing with the meta-data > > > table (your RAND() query) there is much less data that needs to be > > > traversed to get your answer, which should result in a faster query. > > > > > > > > > > > > > > > On Mon, 8 Mar 2004, Neil Gunton wrote: > > > > > > > Hi all, > > > > > > > > I am using MySQL 4.0.x to run a community website which has (among > other > > > > things) over 19,000 pictures. There is a page that selects 30 random > > > > thumbnails. I have noticed that the performance of "ORDER BY RAND()" > on > > > > this table has a significant impact on performace. I have all the > > > > relevant indexes defined, and I have researched this issue on the > Web. > > > > It seems that other people have also encountered a performance hit > while > > > > using ORDER BY RAND(). The reason appears to be that when you do > EXPLAIN > > > > on a query using this, MySQL reports "Using temporary; Using > filesort", > > > > which is the worst possible result. Also, the number of rows > reported is > > > > pretty much the entire set. So, presumably, the current > implementation > > > > of ORDER BY RAND() means that MySQL has to traverse the entire > table, > > > > regardless of other indexes. > > > > > > > > There are, of course, other ways to get around this, but they are > all > > > > more complex than simply using ORDER BY RAND(). I think that > selecting a > > > > random number of records from a table is something that a lot of > > > > websites would like to be able to do, and so as datasets get larger > it > > > > would be nice to see this function scale well. For anyone who has a > > > > website with a large archive of data, the ability to present a &
Re: ORDER BY RAND() performance
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
Donny, what do you do? Throw all the values into an array or something on the client side, and use a random number generator to pull out the array elements? I suppose (depending on resultset size) pulling that many rows from server to client and handing on client side could be faster... On Mon, 8 Mar 2004, Donny Simonton wrote: > ORDER BY RAND(), just sucks in my opinion. We have created our own internal > randomization system because pretty much everytime you use it will show up > in the slow query log, because of the using temporary, using filesort it > does. Splitting your data into a hundred tables will still make it "using > temporary, using filesort". > > I just did a little test, where I only had 5 entries in a table, and I using > temp using filesort. > > Will it ever be improved? Probably the same time order by DESC is improved. > > Donny > > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Monday, March 08, 2004 2:05 PM > > To: Neil Gunton > > Cc: MySQL > > Subject: Re: ORDER BY RAND() performance > > > > > > If your infact (sounds like) storing the pictures meta-data (name, size, > > owner, etc) and the data (blob of some kind) .. I would definately break > > up the design into 2 tables. That way when dealing with the meta-data > > table (your RAND() query) there is much less data that needs to be > > traversed to get your answer, which should result in a faster query. > > > > > > > > > > On Mon, 8 Mar 2004, Neil Gunton wrote: > > > > > Hi all, > > > > > > I am using MySQL 4.0.x to run a community website which has (among other > > > things) over 19,000 pictures. There is a page that selects 30 random > > > thumbnails. I have noticed that the performance of "ORDER BY RAND()" on > > > this table has a significant impact on performace. I have all the > > > relevant indexes defined, and I have researched this issue on the Web. > > > It seems that other people have also encountered a performance hit while > > > using ORDER BY RAND(). The reason appears to be that when you do EXPLAIN > > > on a query using this, MySQL reports "Using temporary; Using filesort", > > > which is the worst possible result. Also, the number of rows reported is > > > pretty much the entire set. So, presumably, the current implementation > > > of ORDER BY RAND() means that MySQL has to traverse the entire table, > > > regardless of other indexes. > > > > > > There are, of course, other ways to get around this, but they are all > > > more complex than simply using ORDER BY RAND(). I think that selecting a > > > random number of records from a table is something that a lot of > > > websites would like to be able to do, and so as datasets get larger it > > > would be nice to see this function scale well. For anyone who has a > > > website with a large archive of data, the ability to present a random > > > selection of this data is very useful. > > > > > > I would like to know if anyone knows if the MySQL team is aware of this > > > problem, and if so whether they are planning on improving it at any > > > point. I ask mainly because if I am told that "yes, it'll be much better > > > in version X" then I can live with the couple of seconds that it takes > > > currently, knowing that this will be better down the line. However if I > > > am advised that this is a fundamentally hard problem for whatever > > > reason, then I will put the effort into reworking my tables to use an > > > alternative solution. > > > > > > The only real solution that I can see which is fast is to make another > > > table which contains just the unique IDs of the pictures that are > > > visible (there are others which are not publicly visible, and which > > > shouldn't be included in the random query, so making a separate table > > > with the appropriate subset makes sense for performance). This new table > > > will have a primary key which is a numeric "sequence" field. Every > > > record will have its own sequence number, going from 1 up to the number > > > of records. Then, instead of doing one query with ORDER BY RAND() LIMIT > > > 30, I can instead do 30 queries, each with a different random sequence > > > (generated from Perl), which will look up the unique sequence number. > > > Since this is a primary key, it will be very fast, so that doing 30 > > > queries will not have a big perfo
RE: ORDER BY RAND() performance
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
[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
If your infact (sounds like) storing the pictures meta-data (name, size, owner, etc) and the data (blob of some kind) .. I would definately break up the design into 2 tables. That way when dealing with the meta-data table (your RAND() query) there is much less data that needs to be traversed to get your answer, which should result in a faster query. On Mon, 8 Mar 2004, Neil Gunton wrote: > Hi all, > > I am using MySQL 4.0.x to run a community website which has (among other > things) over 19,000 pictures. There is a page that selects 30 random > thumbnails. I have noticed that the performance of "ORDER BY RAND()" on > this table has a significant impact on performace. I have all the > relevant indexes defined, and I have researched this issue on the Web. > It seems that other people have also encountered a performance hit while > using ORDER BY RAND(). The reason appears to be that when you do EXPLAIN > on a query using this, MySQL reports "Using temporary; Using filesort", > which is the worst possible result. Also, the number of rows reported is > pretty much the entire set. So, presumably, the current implementation > of ORDER BY RAND() means that MySQL has to traverse the entire table, > regardless of other indexes. > > There are, of course, other ways to get around this, but they are all > more complex than simply using ORDER BY RAND(). I think that selecting a > random number of records from a table is something that a lot of > websites would like to be able to do, and so as datasets get larger it > would be nice to see this function scale well. For anyone who has a > website with a large archive of data, the ability to present a random > selection of this data is very useful. > > I would like to know if anyone knows if the MySQL team is aware of this > problem, and if so whether they are planning on improving it at any > point. I ask mainly because if I am told that "yes, it'll be much better > in version X" then I can live with the couple of seconds that it takes > currently, knowing that this will be better down the line. However if I > am advised that this is a fundamentally hard problem for whatever > reason, then I will put the effort into reworking my tables to use an > alternative solution. > > The only real solution that I can see which is fast is to make another > table which contains just the unique IDs of the pictures that are > visible (there are others which are not publicly visible, and which > shouldn't be included in the random query, so making a separate table > with the appropriate subset makes sense for performance). This new table > will have a primary key which is a numeric "sequence" field. Every > record will have its own sequence number, going from 1 up to the number > of records. Then, instead of doing one query with ORDER BY RAND() LIMIT > 30, I can instead do 30 queries, each with a different random sequence > (generated from Perl), which will look up the unique sequence number. > Since this is a primary key, it will be very fast, so that doing 30 > queries will not have a big performance impact. However this scheme > requires that the sequences in the new table be kept very consistent - > for example, if a picture is removed from the sequence then the sequence > numbers above that record have to be updated. This introduces potential > for error, but it is a possible solution. I don't want to implement it, > obviously, if ORDER BY RAND() is slated for improvement. > > Thanks for any ideas or insights... > > -Neil Gunton > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY RAND() performance
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: RAND ()
Hi Payne,
This is probably because of a bug in MySQL versions before 3.23.56 and
4.0.10 -- RAND() didn't work right the first time it was used in a *new*
connection. After the first run, though, it was OK.
To work around the problem, I've told people to just call RAND() a few
times before doing your query. Just like this in PHP:
mysql_query('SELECT RAND(), RAND(), RAND()');
Then the ORDER BY RAND() query should be random. Now that I think about
it, putting RAND() twice in the ORDER BY would probably have the same
effect. So you could try
$result = mysql_query('... ORDER BY RAND(), RAND() LIMIT 1');
Hope that helps.
Matt
- Original Message -
From: "Payne"
Sent: Tuesday, November 04, 2003 10:33 AM
Subject: RAND ()
> Hi,
>
> I have been playing around with RAND(). It works very well if I do a
sql
> statement with mysql, but I having problem using with mysql statement
> with php. When a person calls on the page, the same output is always
> view. How can I get RAND() work with php.
>
> sorry for the php, stuff. Thanks for any help you can give.
>
> Payne
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RAND ()
Hi,
I have been playing around with RAND(). It works very well if I do a sql
statement with mysql, but I having problem using with mysql statement
with php. When a person calls on the page, the same output is always
view. How can I get RAND() work with php.
sorry for the php, stuff. Thanks for any help you can give.
Payne
$db = mysql_connect("127.0.0.0","fred","mrbill");
mysql_select_db("links",$db);
$result = mysql_query("SELECT url FROM sponsors order by
rand() LIMIT 1", $db);
if ($myrow = mysql_fetch_array($result)) {
echo"";
do {
printf("%s\n",$myrow[url]);
} while ($myrow = mysql_fetch_array($result));
} else {
echo "Sorry, no message of day today";
}
echo ""
?>
--
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 -=-=-=-=-
Re: Problem With RAND()
Hi! On Jul 07, Scott A. Hammond, Sr. wrote: > I am running MySQL 3.23.54. > > Query: SELECT column1, column2, RAND() AS column3 FROM table WHERE column2 > IS NOT NULL ORDER BY column3 LIMIT 10; > > I am running this query within a PHP page that uses mysql_connect. More > then 9 out of 10 times I get the first 10 rows of the database in order, > i.e. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Every once in a while, I get it in a > different order, however, very rarely. This exact PHP file works on another > server which is running an earlier version of MySQL (3.23.41). Moved to > this server, I can't seem to pull 10 random rows from the database. > > I've tried initializing RAND with a number but that didn't help. I'd > consider just generating the random numbers myself and selecting the rows, > however, I need the WHERE column2 IS NOT NULL as part of the query so it > needs to be randomized within MySQL. > > I've altered the program to echo column3 (the random number) and I get a > different set of numbers each time so it isn't a cached result or anything > like that. The numbers are simply coming out in the same order as the rows > are originally in the database on most attempts. > > I tried the query from command line (MySQL client) and the results seem to > be more randomized then when called via PHP, however, I'm having a hard time > finding any clues on either end (MySQL or PHP) as to why I'm not getting > random results. This query works on my other servers (which are running > earlier versions of MySQL). > > Any thoughts or help is appreciated. > > Scott According to the manual: Changes in release 3.23.56 (13 Mar 2003): ... * Better `RAND()' initialisation for new connections. ... Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With RAND()
Since you are ordering by column3, ( in other words sorting your random mumbers ), what do you expect? Scott A. Hammond, Sr. wrote: I am running MySQL 3.23.54. Query: SELECT column1, column2, RAND() AS column3 FROM table WHERE column2 IS NOT NULL ORDER BY column3 LIMIT 10; I am running this query within a PHP page that uses mysql_connect. More then 9 out of 10 times I get the first 10 rows of the database in order, i.e. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Every once in a while, I get it in a different order, however, very rarely. This exact PHP file works on another server which is running an earlier version of MySQL (3.23.41). Moved to this server, I can't seem to pull 10 random rows from the database. I've tried initializing RAND with a number but that didn't help. I'd consider just generating the random numbers myself and selecting the rows, however, I need the WHERE column2 IS NOT NULL as part of the query so it needs to be randomized within MySQL. I've altered the program to echo column3 (the random number) and I get a different set of numbers each time so it isn't a cached result or anything like that. The numbers are simply coming out in the same order as the rows are originally in the database on most attempts. I tried the query from command line (MySQL client) and the results seem to be more randomized then when called via PHP, however, I'm having a hard time finding any clues on either end (MySQL or PHP) as to why I'm not getting random results. This query works on my other servers (which are running earlier versions of MySQL). Any thoughts or help is appreciated. Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem With RAND()
I am running MySQL 3.23.54. Query: SELECT column1, column2, RAND() AS column3 FROM table WHERE column2 IS NOT NULL ORDER BY column3 LIMIT 10; I am running this query within a PHP page that uses mysql_connect. More then 9 out of 10 times I get the first 10 rows of the database in order, i.e. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Every once in a while, I get it in a different order, however, very rarely. This exact PHP file works on another server which is running an earlier version of MySQL (3.23.41). Moved to this server, I can't seem to pull 10 random rows from the database. I've tried initializing RAND with a number but that didn't help. I'd consider just generating the random numbers myself and selecting the rows, however, I need the WHERE column2 IS NOT NULL as part of the query so it needs to be randomized within MySQL. I've altered the program to echo column3 (the random number) and I get a different set of numbers each time so it isn't a cached result or anything like that. The numbers are simply coming out in the same order as the rows are originally in the database on most attempts. I tried the query from command line (MySQL client) and the results seem to be more randomized then when called via PHP, however, I'm having a hard time finding any clues on either end (MySQL or PHP) as to why I'm not getting random results. This query works on my other servers (which are running earlier versions of MySQL). Any thoughts or help is appreciated. Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAND() isn't :)
On Fri, Feb 28, 2003 at 05:19:15PM +1100, Justin French wrote: > Hi all, > > On my LAN server (FreeBSD, MySQL 3.32) the following query works fine, and > seems to return 2 random rows from the table: > > SELECT * FROM disc ORDER BY RAND() LIMIT 2 > > However, when I upload the scripts to the live server (Linux, MySQL 3.32), > the results are VERY un-random -- only very occasionally do the results > differ between each query, usually returning the same two rows. > > The data in the two tables isn't identical, but both tables DO contain the > same number of rows, with the same IDs. > > > Where should I be looking for reasons why the RAND() isn't very random??? Consider upgrading - the problem should be fixed in the most recent versions of 3.23.x and 4.0.x Cheers! -- Zak Greant <[EMAIL PROTECTED]> MySQL AB Community Advocate Personal Blog: http://zak.fooassociates.com Support Global Human Rights - Amnesty International (http://amnesty.org) - 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
RAND() isn't :)
Hi all, On my LAN server (FreeBSD, MySQL 3.32) the following query works fine, and seems to return 2 random rows from the table: SELECT * FROM disc ORDER BY RAND() LIMIT 2 However, when I upload the scripts to the live server (Linux, MySQL 3.32), the results are VERY un-random -- only very occasionally do the results differ between each query, usually returning the same two rows. The data in the two tables isn't identical, but both tables DO contain the same number of rows, with the same IDs. Where should I be looking for reasons why the RAND() isn't very random??? TIA Justin sql,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: RAND is mysql 3.22
hi guys i just tried this on my works old mysql server and it doesnt seem to work , is it possible ? :| - 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: RAND() Problems in mysql version 3.23.54 and 3.23.55 (linux)
Hi! On Feb 05, Tue Tønning wrote: > Hey, > > I have discovered a strange error in the mysql versions mentioned in subj. > > Example. > A table with 10 entries ...i want to draw 3 randomly > => "select fieldName from tableName order by RAND() limit 3" > > this have worked for the last year or so.but after upgrading our redhat > 7.2 with mysql version 3.23.54 it stopped to work. It always chooses the > same 3 entires. try simply mysql -BNe 'select rand(), rand(), rand(), rand()' mysql -BNe 'select rand(), rand(), rand(), rand()' mysql -BNe 'select rand(), rand(), rand(), rand()' to see the problem. RAND() initialization for new connection isn't very random, so first few rand() values differ only slightly. It was fixed in 4.0. I will backport the fix to 3.23. You can either upgrade to MySQL 4.0, or wait till next 3.23 release, or use a simple workaround: run do benchmark(10,rand()); just after establishing connection - before first SELECT. > Ohh, yeah - we code in PHP..and i have talked today with 3 other > webdevelopment companies that have been struggeling with the same error > after upgrading. Easy - no need to struggling, just submit a bugreport - the bug will be fixed at once :) Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ - 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: RAND() Problems in mysql version 3.23.54 and 3.23.55 (linux)
HINT: I have used PERL to generate random numbers and then do a SELECT on the auto_increment field in the mysql table, thus generating random picks from my tables; I'll be glad to bet informed of better ways around, though :) Thanks Aman Raheja AGF Inc. - Original Message - From: "Tue Tønning" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, February 05, 2003 1:38 PM Subject: RAND() Problems in mysql version 3.23.54 and 3.23.55 (linux) > Hey, > > I have discovered a strange error in the mysql versions mentioned in subj. > > Example. > A table with 10 entries ...i want to draw 3 randomly > => "select fieldName from tableName order by RAND() limit 3" > > this have worked for the last year or so.but after upgrading our redhat > 7.2 with mysql version 3.23.54 it stopped to work. It always chooses the > same 3 entires. > > using a Seed doesnt make it perfect either (i know that the manual says that > RAND() isnt perfect - but now it doesnt work at all). > > A quick search on google says me that im not the only one having the > problem. I can also see on some Danish messageboards that other have the > same problems with the new version. > > So my question is .What to do ? > I cant imagine that you have removed the functionality of RAND from mysql > again ? > i know its only been in there since early ~3.23 > > I work professionally as a webdevolper (CEO of a firm) and we need to find a > solution to this problem badly. > > I hope you can guide me to a solution. Cause i would hate to change all the > sql calls in the projects we have coded the last 1½ year and it would be > nice to know if we would be able to use RAND() in the future. > > Ohh, yeah - we code in PHP..and i have talked today with 3 other > webdevelopment companies that have been struggeling with the same error > after upgrading. > > Med venlig hilsen / Best regards > > Tue Tønning > > AT-Orbital I/S > Helsingforsgade 27, 1 > DK-8200 Aarhus N > Denmark > Phone +45 8942 5860 > Web www.atorbital.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
RAND() Problems in mysql version 3.23.54 and 3.23.55 (linux)
Hey, I have discovered a strange error in the mysql versions mentioned in subj. Example. A table with 10 entries ...i want to draw 3 randomly => "select fieldName from tableName order by RAND() limit 3" this have worked for the last year or so.but after upgrading our redhat 7.2 with mysql version 3.23.54 it stopped to work. It always chooses the same 3 entires. using a Seed doesnt make it perfect either (i know that the manual says that RAND() isnt perfect - but now it doesnt work at all). A quick search on google says me that im not the only one having the problem. I can also see on some Danish messageboards that other have the same problems with the new version. So my question is .What to do ? I cant imagine that you have removed the functionality of RAND from mysql again ? i know its only been in there since early ~3.23 I work professionally as a webdevolper (CEO of a firm) and we need to find a solution to this problem badly. I hope you can guide me to a solution. Cause i would hate to change all the sql calls in the projects we have coded the last 1½ year and it would be nice to know if we would be able to use RAND() in the future. Ohh, yeah - we code in PHP..and i have talked today with 3 other webdevelopment companies that have been struggeling with the same error after upgrading. Med venlig hilsen / Best regards Tue Tønning AT-Orbital I/S Helsingforsgade 27, 1 DK-8200 Aarhus N Denmark Phone +45 8942 5860 Web www.atorbital.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
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=13287&edit=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=13287&edit=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
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 . " " . $row->ClientName . "");
}
This worked fine for me. Maybe there is not a space between NULL and ORDER?
Jay
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ORDER BY RAND() not working
> $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
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
[snip] I'm running PHP 4.1.2 and MySQL 3.23.39. I have a simple query that works if I telnet into MySQL and run the query manually. If I try and use it in a PHP page, I get the "Supplied argument is not a valid MySQL result resource" error. However, if I take out the ORDER BY RAND() part from the query, it works in PHP. Doesn't work: SELECT * FROM products WHERE sale_price IS NOT NULL ORDER BY RAND() LIMIT 3 Works: SELECT * FROM products WHERE sale_price IS NOT NULL LIMIT 3 Does anyone have any idea why this is happening? [/snip] Need to see your PHP code, that's where it's broken. Jay sql, mysql, query :^) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ORDER BY RAND() not working
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
rand() ... limit by .... & my retrivals
hi guys
im trying to retrieve 5 record each page from my db
my code iz :
$query_nme = "SELECT firstName, lastName, title, summary FROM ctnt_inf
where ctnt_id ORDER BY RAND() LIMIT 5";
$query_result_handle_nme = mysql_query ($query_nme)
or die ('qry failed ! DA tbl must xixt in DA db specifyd bov ');
echo("");
for ($cnt=0; $cnt<=5; ++$cnt ) {
$row_nme = mysql_fetch_row ($query_result_handle_nme);
echo ("");
$tID = $row_id[0];
$tName = $row_nme[0];
echo ( $tID );
echo ( $tName );
} // echo ("");
echo("");
problem:
it only displays firstName n nothing else though 5 random first names
but
i guess i'll ve to make seperate query fo each field in the table
or will i
thnx a million .
toby .
_
Join the worlds largest e-mail service with MSN Hotmail.
http://www.hotmail.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: Rand slowness.
Ignoring the limit problem for just a moment, what are the performance characteristics? Does this form have a less steep rows vs. time curve? As for the limit problem, that sounds a bit like a bug in MySQL and should be addressed as such. -JF > -Original Message- > From: MySQL List [mailto:[EMAIL PROTECTED]] > Sent: Thursday, June 27, 2002 2:44 PM > To: Jon Frisby > Cc: mysql > Subject: Re: Rand slowness. > > > I tried this, but it seems to be ignoring the LIMIT 3, since it > is returning > all the rows. > > > > Try: > > SELECT RAND() AS r, * FROM table ORDER BY r LIMIT 3; > > > > Off the top of my head, I'd guess that the reason for the > slowdown has to > do > > with MySQL doing a new RAND() call for every comparison in the sort > > operation -- it wont stop sorting until it happens to get values back in > the > > comparisons indicating that it has a "correct" ordering. The > odds of this > > happening in a timely fashion *decrease* quite quickly as the number of > > comparisons increases. > > > > Selecting a random value *PER ROW* ensures that a comparison between any > two > > given rows will always have the same result the second time you > try it as > it > > did the first time you try it and will thus ensure deterministic and > > reasonable sorting behavior. > > > > -JF > > > > > -Original Message- > > > From: MySQL List [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, June 27, 2002 12:05 PM > > > To: mysql > > > Subject: Rand slowness. > > > > > > > > > Hi, > > > Is there a quicker way to get a selection of random rows than RAND()? > > > I basically use: > > > > > > SELECT * FROM table ORDER BY RAND() LIMIT 3; > > > > > > But I find that as the # of rows increases in table, it keeps > > > taking longer > > > and longer to get a result back. And I am talking a large increase, > > > basically 50 rows took about 1 second, 700 rows now takes almost a > minute. > > > > > > I am not sure why the # of rows would affect RAND. > > > > > > I am using the latest version of Mysql and php4 to get the rows. > > > > > > > > > - > > > 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
RAND slowness?
Hi, Can someone tell me which query would be faster with say 1000 rows: SELECT * FROM table ORDER BY RAND() LIMIT 3; or SELECT * FROM table; then use php to get 3 random rows from the returned data? - 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: Rand slowness.
I tried this, but it seems to be ignoring the LIMIT 3, since it is returning all the rows. > Try: > SELECT RAND() AS r, * FROM table ORDER BY r LIMIT 3; > > Off the top of my head, I'd guess that the reason for the slowdown has to do > with MySQL doing a new RAND() call for every comparison in the sort > operation -- it wont stop sorting until it happens to get values back in the > comparisons indicating that it has a "correct" ordering. The odds of this > happening in a timely fashion *decrease* quite quickly as the number of > comparisons increases. > > Selecting a random value *PER ROW* ensures that a comparison between any two > given rows will always have the same result the second time you try it as it > did the first time you try it and will thus ensure deterministic and > reasonable sorting behavior. > > -JF > > > -Original Message- > > From: MySQL List [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, June 27, 2002 12:05 PM > > To: mysql > > Subject: Rand slowness. > > > > > > Hi, > > Is there a quicker way to get a selection of random rows than RAND()? > > I basically use: > > > > SELECT * FROM table ORDER BY RAND() LIMIT 3; > > > > But I find that as the # of rows increases in table, it keeps > > taking longer > > and longer to get a result back. And I am talking a large increase, > > basically 50 rows took about 1 second, 700 rows now takes almost a minute. > > > > I am not sure why the # of rows would affect RAND. > > > > I am using the latest version of Mysql and php4 to get the rows. > > > > > > - > > 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
Rand slowness.
Hi, Is there a quicker way to get a selection of random rows than RAND()? I basically use: SELECT * FROM table ORDER BY RAND() LIMIT 3; But I find that as the # of rows increases in table, it keeps taking longer and longer to get a result back. And I am talking a large increase, basically 50 rows took about 1 second, 700 rows now takes almost a minute. I am not sure why the # of rows would affect RAND. I am using the latest version of Mysql and php4 to get the rows. - 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: RAND and MySQL version!
You can't do "ORDER BY RAND()" prior to version 3.23... On your 3.22.32 version do something like: SELECT column_name*0+RAND() AS rnd FROM table_name WHERE IsActive=1 ORDER BY rnd LIMIT 1; Gurhan -Original Message- From: Soheil Shaghaghi [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 14, 2002 4:26 PM To: [EMAIL PROTECTED] Subject: RAND and MySQL version! Hello All, I am running 2 MySQL servers, one on Linux, and the other on FreeBSD. Linux, MySQL version: 3.23.49 FreeBSD, MySQL version: 3.22.32 Problem: The following code runs on the newer version of MySQL, and Linux, but when I run it on the other server, I get sql error. SELECT ID,Headlines,IsActive FROM DATABASE WHERE IsActive=1 ORDER BY RAND() LIMIT 1 Can anyone please tell me if it's the version of MySQL causing this problem or something else? Is there anyway to get around this, without upgrading MySQL? Thanks so much, Soheil - 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: RAND and MySQL version!
At 13:26 -0700 5/14/02, Soheil Shaghaghi wrote: >Hello All, >I am running 2 MySQL servers, one on Linux, and the other on FreeBSD. >Linux, MySQL version: 3.23.49 >FreeBSD, MySQL version: 3.22.32 > >Problem: > >The following code runs on the newer version of MySQL, and Linux, but when I >run it on the other server, I get sql error. > >SELECT ID,Headlines,IsActive FROM DATABASE WHERE IsActive=1 ORDER BY RAND() >LIMIT 1 > >Can anyone please tell me if it's the version of MySQL causing this problem >or something else? Yes. Expressions in the ORDER BY clause are not supported prior to 3.23.2. > >Is there anyway to get around this, without upgrading MySQL? SELECT ID*0+RAND() AS r, ID, Headlines, ... ORDER BY r LIMIT 1; > >Thanks so much, >Soheil - 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
RAND and MySQL version!
Hello All, I am running 2 MySQL servers, one on Linux, and the other on FreeBSD. Linux, MySQL version: 3.23.49 FreeBSD, MySQL version: 3.22.32 Problem: The following code runs on the newer version of MySQL, and Linux, but when I run it on the other server, I get sql error. SELECT ID,Headlines,IsActive FROM DATABASE WHERE IsActive=1 ORDER BY RAND() LIMIT 1 Can anyone please tell me if it's the version of MySQL causing this problem or something else? Is there anyway to get around this, without upgrading MySQL? Thanks so much, Soheil - 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: Using RAND()
Quoted from the manual page I have given below is:
"RAND() is not meant to be a perfect random generator, but instead a fast
way to generate ad hoc random numbers that will be portable between
platforms for the same MySQL version."
So it was intentionally built so...
Gurhan
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 07, 2002 12:55 PM
To: [EMAIL PROTECTED]
Subject: Re: Using RAND()
Hi
When I used the RAND() function (MySQL 3.23.45) I found the results were not
very random, instead I use an extra bit of php code to make sure the results
are more random.
mt_srand((double)microtime()*100);
$sql = "select .... order by RAND(".mt_rand(0,32767).") limit 50"
$rs = mysql_query($sql);
$num=mysql_num_rows($rs);
if($num>1) mysql_data_seek($rs,mt_rand(0,$num-1));
$row=mysql_fetch_array($rs);
To get a random record from earlier versions of MySQL you could do something
like this.
mt_srand((double)microtime()*100);
$sql = "select... ..where limit 0,50"
$rs = mysql_query($sql);
$num=mysql_num_rows($rs);
if($num>1) mysql_data_seek($rs,mt_rand(0,$num-1));
$row=mysql_fetch_array($rs);
if you have a lot of records you can always put random numbers in the limit
clause, (select ... where ...limit random_number, random_number + 50) this
will return a random block of records, then use the seek functions to
randomly pick a record from the record block, just make sure random_number
isn't greater than the total records in your recordset.
Pete Kelly
- Original Message -
From: "Gurhan Ozen" <[EMAIL PROTECTED]>
To: "Cummings, Shawn (GNAPs)" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, May 07, 2002 5:11 PM
Subject: RE: Using RAND()
> You have to have MySQL 3.23 or greater..
>
> http://www.mysql.com/doc/M/a/Mathematical_functions.html
>
> Gurhan
>
> -Original Message-
> From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, May 07, 2002 11:33 AM
> To: [EMAIL PROTECTED]; Ulf Harnhammar
> Cc: [EMAIL PROTECTED]
> Subject: Using RAND()
>
>
>
>
> The RAND() syntax does not appear to be working for me.
>
> What version mySQL is required?
>
>
> At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote:
>
> >Hi!
> >
> > >>>>> "Ulf" == Ulf Harnhammar <[EMAIL PROTECTED]> writes:
> >
> >Ulf> On Sun, 27 Jan 2002, Michael Widenius wrote:
> > >> One way to do this is to do as follows:
> > >> SELECT something FROM sometable WHERE somevar=somevalue ORDER BY
> > >> RAND() LIMIT 10
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
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
