Re: Efficiently finding a random record
As it happens, I found this post useful tonight for an almost identical situation. I have a table with exactly 200,000 rows, the first column is an autoincrement ID field. I am confident that all IDs are consecutive and there are no holes. When I do: Select ID from history where id = CAST( rand() * 19 + 1 as UNSIGNED) limit 1; shouldn't it always return a single record? The CAST id should always be in the range from 1 to 20. About 50% of the time I get an empty recordset and I'm puzzled. (could be lack of sleep though) Gary - Original Message - From: "Eric Bergen" <[EMAIL PROTECTED]> To: "Dan Bolser" <[EMAIL PROTECTED]> Cc: "Philip Hallstrom" <[EMAIL PROTECTED]>; "Brian Dunning" <[EMAIL PROTECTED]>; Sent: Friday, May 13, 2005 7:48 PM Subject: Re: Efficiently finding a random record in() can take millions of arguments. Up to max packet size. Try it :) Dan Bolser wrote: On Fri, 13 May 2005, Eric Bergen wrote: Even better is if you have an integer primary key (think auto_increment) and use in() So if you want 10 random rows in your app generate 20 or so random numbers and do something like select col1, from t where x in (1, 5, 3, 2...) limit 10 check num rows and if you don't get enough generate more random numbers and try again. in() is blazing fast even with thousands of numbers so don't be afraid to kick a few extra in. I heard about a 255 'in' limit. When you say 'thousands of numbers' do you mean in the IN or in the column? -Eric Philip Hallstrom wrote: I have a db of about 300,000 records and when I try to find one random record like this: select * from table order by rand() limit 1; it can take several minutes. My Sherlock Holmes instincts tell me that what I'm doing is somehow inefficient. What is the primary culprit here? The culprit is that mysql has to assign a random value (via rand()) to each of the 300,000 rows, then order all 300,000 rows by that random value and return the first one. So even though you're just retrieving one value, you're processing 300,000 rows. You'd be better off doing something like this in your application.. row_count = get row count; random_row = get random number from 0 to row_count - 1; result = db query LIMIT 1 OFFSET random_row or something like that... -philip -- 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: SLOW SLOW query - please help!
Hi, with a table with 33 rows, i have the row in 0s using index on term. mysql> select * from tx where term like 'Britney Spears' ; +++ | id | term | +++ | 327681 | Britney Spears | +++ 1 row in set (0.00 sec) mysql> explain select * from tx where term = 'Britney Spears' ; ++-+---+---+---+--+-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+---+--+-+ | 1 | SIMPLE | tx| const | term | term | 256 | const | 1 | Using index | ++-+---+---+---+--+-+---+--+-+ 1 row in set (0.00 sec) hope that helps. Even with lock tables you may have the row fast. Mathias Selon Brian Dunning <[EMAIL PROTECTED]>: > > What is the output of the following: > > EXPLAIN select * from terms where term like 'Britney Spears' limit 1; > > select_type = SIMPLE > table = terms > type = range > possible_keys = term, term_2 (I just created term_2, fulltext) > key = term (this means it's not using the one I just created?) > key_len = 255 > ref = NULL > Extra = Using where > > > Also, can you use "=" instead of "like" in your query? > > Yes, I made that change and it still works, so I'll leave it. > > -- > 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: Read past Equivalent in MySQL
hi, i followed this thread and really think that this isn't a locking problem, but a table structure problem. if there is a column in table with a boolean flag (dealt yes/no) the queries go just looking for rows where dealt=0 (or no). Mathias Selon Duncan Hill <[EMAIL PROTECTED]>: > On Friday 13 May 2005 18:21, Gordon wrote: > > If you can add a table structure why not create a SELECTED table with > > REPORT ID and PERSON ID as the 2 field PRIMARY KEY. > > > > Then you could INSERT IGNORE into this table [with no BEGN/COMMIT] and the > > IGNORE would throw away those already selected. > > Mostly because I've glossed over the exact details of what I'm doing. The > table in question is a reporting table storing a list of IDs (multiple per > key) and things like purge time etc. When the reporting script runs, it > needs to deal with rows that haven't been dealt with before, but not with > rows that are currently being processed by another reporting script that's > already processing rows not dealt with before. > > In theory, no two scripts should ever access the table at the same time - > they > run 15 minutes apart. However, I'd rather not leave it to chance. > > -- > 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]
