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]
