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]
