EXPLAIN EXTENDED

How to create fast database queries

Archive for March 1st, 2009

Selecting random rows

with 14 comments

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 »

Written by Quassnoi

March 1st, 2009 at 9:00 pm

Posted in MySQL