Mysql mysql и случайная выборка с условием, кто знает секреты оптимизации?

[Ocularis]

В общем пришлось мне как то столкнуться с такой задачей:
Есть массив ARR [1213,4584965165,5552654,...] из 3000-5000 значений, состоящий из 1-15 значных целых чисел, часто повторяющихся.
Есть таблица, в ней 2,5 миллиона строк, примерного вида:

ID    NUM

1    123
2    986565
3    8456321
3    123
3    986565

... и так далее. и ID и NUM не уникальны.
Есть вторая таблица с уникальными ID, вида:

ID    WORD
1    стол
2    стул
3    шкаф

В ней примерно 1,4 миллиона строк.

Задача следующая:
Нужно получить случайные WORD и соответствующие NUM в количестве изначального массива ARR (3000-5000).
но не просто совсем случайный, а те у которых NUM совпадает со значением из ARR, и id.table1=id.table2.

Обработав ARR можно получить примерно 300 уникальных целых чисел, по которым нужно сделать разноразмерную выборку.
Немного помучившись и протестировав 3 различных решения для случайной выборкой по условию. Вернулся к изначальному, хоть оно и считается самым медленным, но почему-то в моем частном случае оказалось быстрее всех (но не значительно).

SELECT t1.id,t1.num
FROM table1 t1
WHERE t1.num= :input1
ORDER BY rand()
LIMIT :input2

Поскольку нам нужно получить совершенно разное количество строк из разных групп NUM, и потом еще сделать join с второй таблицой, ничего хитрее чем сделать в лоб не придумал.

SELECT t2.word, z.num FROM (
(SELECT t1.id,t1.num
FROM table1 t1
WHERE t1.num= 1213
ORDER BY rand()
LIMIT 2)
UNION ALL
(SELECT t1.id,t1.num
FROM table1 t1
WHERE t1.num= 4584965165
ORDER BY rand()
LIMIT 4)
UNION ALL
(SELECT t1.id,t1.num
FROM table1 t1
WHERE t1.num= 5552654
ORDER BY rand()
LIMIT 70)
... и так еще 297 раз
) z LEFT JOIN table2 t2 ON z.id=t2.id

И выполнение всего этого занимает примерно 22-25 секунд.

Еще пробовал воспользоваться вот таким решением, но прироста производительности оно не дало.
http://habrahabr.ru/post/207096/

Есть ли у кого идеи как можно решить это более правильным и быстрым решением?

 

[fixxxer]

Одну случайную строку по PK эффективно выбрать легко:

select * from T where id >= (select round(rand() * (select id from T order by id desc limit 1))) order by id limit 1;

- разумеется, равномерное распределение будет нарушено плотностью дырок в id, но для крупной таблицы это несущественно.

Нужное число можно набрать тупо сделав необходимое число запросов в цикле, добавляя условие where id not in (уже выбранные id). Как вариант, генерировать union, после чего при необходимости "добирать".

Для твоего случая еще немного надо доработать, но смысл ясен.

 

[Ocularis]

Но помимо просто выборки случайного id, нужно еще что бы num этого id было равно значению из ARR, то есть в выборке уже изначально заложены приличные дыры. так как выборку делаем по условию.

select * from T where id >= (select round(rand() * (select id from T order by id desc limit 1))) order by id limit 1;

А так же этот запрос далеко не случайное выдает. в таблице в которой id от 1 до 2456358 без дырок, запрос всегда выдает id не больше 4 значного. что при случайной выборке статистически не возможно, так как эта "случайность" почему-то всегда попадает в диапазон который меньше 1% от общего числа значений id.

Странно, но вот такая обертка делает это уже правильно.

SET @rnd = (select round(rand()  * (select id from T order by id desc limit 1)));
select *  from T where id >= @rnd
order by id limit 1;

но проблема остается, что делать когда приходиться ограничивать выборку в таблице условием. а значит полного диапазона id в выборке уже 100% не будет.

 

[fixxxer]

Есть предположение, что если брать так:

... where id >= randomValue and num in (yourArray) .. order by id limit 1

то будет достаточно ок. Но не уверен, хз, какое у тебя распределение.

Если так не получится, то надо думать над дополнительными структурами, а тут уже нужно знать, откуда этот arr берется, как часто меняется и т.д.

Еще, возможно, вариант - выбрать все, где num из yourArray, во временную in-memory таблицу, а уже из нее рандомизировать.