Bug #33837 | order by rand() speed | ||
---|---|---|---|
Submitted: | 12 Jan 2008 20:48 | Modified: | 3 May 2012 15:08 |
Reporter: | anzenews asdf | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
Version: | any | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | order by rand, speed |
[12 Jan 2008 20:48]
anzenews asdf
[14 Jan 2008 12:58]
Susanne Ebrecht
Many thanks for writing a feature request. This can't be changed. select * from tab order by something limit 3; always means a sequential scan of the table. Because you only can get the first three results, when you know the whole results.
[14 Jan 2008 13:36]
anzenews asdf
... which is exactly why this is a special case: if you are looking for _RANDOM_ results there is no need to make a sequential scan. You could make a random scan and stop after X found records... I appreciate that you have a deeper understanding of the system and how it could be incorporated but this really is only solveable on MySQL level. All higher-level implementations are cumbersome, limited or even impossible to do. Right now "ORDER BY RAND()" is a very common and at the same time very inefficient query... Thanks for the answer though, I hope you change your mind about this...
[26 Mar 2008 10:24]
Susanne Ebrecht
Many thanks for writing a feature request. We will check if we can implement this or not.
[2 May 2012 18:46]
Sveta Smirnova
There is verified bug #65177 with patch proposed about very same problem, so closing this one as duplicate of that.
[3 May 2012 15:08]
anzenews asdf
I don't use MySQL anymore in my products, so this is not very important to me anymore. However, there are a few points I would like to make: - proposed solution apparently works better only when used on smaller number of rows - proposed solution only makes a small(ish) speed difference (less than factor 2), at least as shown in poster's example In other words, the new ticket is more specific and does not solve this problem adequately (IMHO, of course). If you first order and then limit records there can be no big speedup because you need to re-order elements each time and that takes a lot of space / time. What I was trying to point out is that this is a special case where ordering of records is not needed at all. Granted, I do not have a patch, and I am still happy to see something done in this direction. Anyway, feel free to close the ticket, I am already on MongoDB. :)