EXPLAIN EXTENDED

Usual way to select random rows from a MySQL table looks like this:

SELECT  *
FROM    t_random
ORDER BY
RAND(20090301)
LIMIT 10

This creates a fake RAND() column along with the query columns, sorts on it and returns bottom 10 results.

EXPLAIN EXTENDED on this query returns quite expected Using temporary; Using filesort

This plan will use Θ(N×log(N)) time on sorting alone. If temporary will hit memory limits, the time will increase even more rapidly.
Read the rest of this entry »