Should I use derived table in this situation?

Question

I need to fetch 10 random rows from a table, the query below will not do it as it is going to be very slow on a large scale (I've read strong arguments against it):

SELECT `title` FROM table1 WHERE id1 = 10527 and id2 = 37821 ORDER BY RAND() LIMIT 10;

EXPLAIN:
select_type |    table    | type | possible_keys |  key  | key_len | ref  | rows | Extra          |
------------+-------------+------+---------------+-------+---------+------+------+----------------+ 
SIMPLE      |  table1     |  ref | id1,id2       |  id2  | 5       | const|  7   | Using where; Using temporary; Using filesort

I tried the following workaround:

SELECT * FROM
(SELECT `title`, RAND() as n1
FROM table1
WHERE id1 = 10527 and id2 = 37821) TTA
ORDER BY n1 LIMIT 10;

EXPLAIN:
select_type |    table    | type | possible_keys |  key  | key_len | ref  | rows | Extra          |
------------+-------------+------+---------------+-------+---------+------+------+----------------+ 
PRIMARY     |  <derived2> |  ALL | NULL          |  NULL | NULL    | NULL |  7   | Using filesort |
DERIVED     |    table1   |  ref | id1,id2       |  id2  |   5     |const |  7   | Using where    |

But I’ve read also couple of statements against using derived tables.

Could you please tell me if the latter query is going to make any improvement?

it does not make difference, but I don't have a large table to test it — qwaz, Apr 15 '14 at 21:12
possible duplicate of [MySQL select 10 random rows from 600K rows fast](http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) — Mike Sherrill 'Cat Recall', Apr 15 '14 at 21:17
I've read that one. That's why I was not sure about using the first method. It did not help me though — qwaz, Apr 15 '14 at 21:19

score 1 · Accepted Answer · answered Apr 15 '14 at 21:13

You should try the first method to see if it works for you. If you have an index on table1(id1, id2) and there are not very many occurrences of any given value pair, then the performance is probably fine for what you want to do.

Your second query is going to have somewhat worse performance than the first. The issue with the performance of order by rand() is not the time taken to calculate random numbers. The issue is the order by, and your second query is basically doing the same thing, with the additional overhead of a derived table.

If you know that there were always at least, say, 1000 matching values, then the following would generally work faster:

SELECT `title`
FROM table1
WHERE id1 = 10527 and id2 = 37821 and rand() < 0.05
ORDER BY RAND()
LIMIT 10;

This would take a random sample of about 5% of the data and with 1,000 matching rows, you would almost always have at least 10 rows to choose from.

Thanks. it's clarified it for me – qwaz Apr 15 '14 at 21:20 — qwaz, Apr 15 '14 at 21:20

Should I use derived table in this situation?

1 Answers1