MySQL Forums
Forum List  »  MyISAM

Re: evaluating table locking
Posted by: Rick James
Date: August 30, 2008 10:55AM

MyISAM will lock the whole table to do the update; InnoDB will lock the one row (and possibly the gap between it and the next key). This is not your performance problem.

SELECT ... ORDER BY rand() LIMIT 5
requires reading the whole table, sorting it, then delivering 5 rows.
* This will collide with the Update lock, whether row or table.
* This will not scale -- bigger table = longer query.

There are numerous tricky ways to optimize ORDER BY rand() LIMIT N , search the web for such. Or change your app to avoid it.

Options: ReplyQuote


Subject
Views
Written By
Posted
5320
May 19, 2008 10:32AM
2932
May 21, 2008 09:04PM
2899
May 30, 2008 08:41AM
Re: evaluating table locking
2874
August 30, 2008 10:55AM
2641
January 21, 2009 03:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.