MYSQL RANDOM SELECT UNIQUE ROWS - excluding previously selected rows

Question

I have a table of 16K records
I want to extract random 44 entries
but I don’t want to repeat the same entries more than once (ever)
therefore, I have a list for each user that stores the already used "IDs" as a string, separated by commas in the table.
and I use this list for SELECT ... NOT IN (used_IDs)


The problem is that this list is getting too big and sql call fails due to the size that I consider

Any idea on how to make this more useful?

Questions table: +------+-------+-------+ | id | Qtext | Tags | +------+-------+-------+ Test table: +------+-------+ | id | QIDs | +------+-------+ Results table: +------+-------+-------+ | id | tID | uID | +------+-------+-------+ 

I need to select unique random values ​​from the Questions table based on the results table. (which associates test id with question IDs)

Currently trying to use:

 SELECT DISTINCT `questions`.`ID` FROM `questions`, `tests`, `results` WHERE `questions`.`ID` NOT IN (`tests`.`qIDs`) AND `results`.`uID` = 1 AND `tests`.`ID` = `results`.`tID` AND 4 IN ( `questions`.`tags`) AND "http://www.usmlestep2qna.com" = `provider` ORDER BY RAND() LIMIT 27; 

Any ideas?

Answer 1

Instead of putting usable user ID values ​​in a single column separated by commas, you can create a tall table to store them. This should lead to better preparation.

Answer 2

Instead of using a single row with a (potentially huge) CSV, why not use a beautifully indexed table and an outer join to select unsurpassed records. I have an example from my test database:

 mysql> select * from first; +------+-------+ | id | title | +------+-------+ | 1 | aaaa | | 2 | bbbb | | 3 | cccc | | 4 | NULL | | 6 | gggg | +------+-------+ 5 rows in set (0.00 sec) mysql> select * from second; +------+----------+------+------+-------+------+ | id | first_id | one | two | three | four | +------+----------+------+------+-------+------+ | 1 | 1 | 3 | 0 | 4 | 6 | | 1 | 2 | 4 | 4 | 1 | 2 | | 3 | 3 | 1 | NULL | 3 | 4 | +------+----------+------+------+-------+------+ 3 rows in set (0.00 sec) mysql> select a.id from first a join second b on a.id=b.first_id; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql> select a.id from first a left outer join second b on a.id=b.first_id where b.first_id is null; +------+ | id | +------+ | 4 | | 6 | +------+ 2 rows in set (0.00 sec) 

This should improve your performance quite nicely.