samedi 20 août 2016

Optimize a JOIN ORDER BY RAND() mysql query in a large database

I am working on a project which has a large Question Bank, and for Tests added to the System, 20 questions are fetched on Run-Time dynamically based on the following query:

SELECT Question.* from Question JOIN Test 
ON Question.Subject_ID = Test.Subject_ID 
AND Question.Question_Level = Test.Test_Level 
ORDER BY RAND() 
LIMIT 20;

However, as it is known that the RAND() function the MySQL kills your server I have been looking for better solutions.

Any help would be appreciated to optimize the query to reduce server load and execution time.

P.S. The system has the capability of Deletion too so the AUTO_INCREMENT PRIMARY KEY of the QUESTION and TEST table can have large gaps.




Aucun commentaire:

Enregistrer un commentaire