title: Optimize a JOIN ORDER BY RAND() mysql query in a large database
body: <p>I am working on a project which has a large <code>Question</code> Bank, and for <code>Tests added</code> to the System, 20 questions are fetched on Run-Time dynamically based on the following query:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>However, as it is known that the <code>RAND()</code> function the MySQL kills your server I have been looking for better solutions.</p>
  
  <p><strong>Result of</strong> <code>EXPLAIN [above query]</code>:</p>
  
  <pre><code>+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
  | id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
  +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
  |  1 | SIMPLE      | Test     | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using temporary; Using filesort                    |
  |  1 | SIMPLE      | Question | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where; Using join buffer (Block Nested Loop) |
  +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
  </code></pre>
  
  <p><strong>Result of</strong> <code>EXPLAIN Question</code>:</p>
  
  <pre><code>+-------------------+------------------------------------------+------+-----+---------+----------------+
  | Field             | Type                                     | Null | Key | Default | Extra          |
  +-------------------+------------------------------------------+------+-----+---------+----------------+
  | Question_ID       | int(11)                                  | NO   | PRI | NULL    | auto_increment |
  | Questions         | varchar(100)                             | NO   |     | NULL    |                |
  | Available_Options | varchar(200)                             | NO   |     | NULL    |                |
  | Correct_Answer    | varchar(50)                              | NO   |     | NULL    |                |
  | Subject_ID        | int(11)                                  | NO   |     | NULL    |                |
  | Question_Level    | enum('Beginner','Intermediate','Expert') | NO   |     | NULL    |                |
  | Created_By        | int(11)                                  | NO   |     | NULL    |                |
  +-------------------+------------------------------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p><strong>Result of</strong> <code>EXPLAIN Test</code>:</p>
  
  <pre><code>+----------------+------------------------------------------+------+-----+---------+----------------+
  | Field          | Type                                     | Null | Key | Default | Extra          |
  +----------------+------------------------------------------+------+-----+---------+----------------+
  | Test_ID        | int(11)                                  | NO   | PRI | NULL    | auto_increment |
  | Test_Name      | varchar(50)                              | NO   |     | NULL    |                |
  | Test_Level     | enum('Beginner','Intermediate','Expert') | NO   |     | NULL    |                |
  | Subject_ID     | int(11)                                  | NO   |     | NULL    |                |
  | Question_Count | int(11)                                  | NO   |     | NULL    |                |
  | Created_By     | int(11)                                  | NO   |     | NULL    |                |
  +----------------+------------------------------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Any help would be appreciated to optimize the query to reduce server load and execution time.</p>
  
  <p><strong>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.</strong></p>