title: How to quickly SELECT 3 random records from a 30k MySQL table with a where filter by a single query?
body: <p>Well, this is a very old question never gotten real solution. We want 3 random rows from a table with about 30k records. The table is not so big in point of view MySQL, but if it represents products of a store, it's representative. The random selection is useful when one presents 3 random products in a webpage for example. We would like a single SQL string solution that meets these conditions:</p>
  <ol>
  <li>In PHP, the recordset by PDO or MySQLi must have exactly 3 rows.</li>
  <li>They have to be obtained by a single MySQL query without Stored Procedure used.</li>
  <li>The solution must be quick as for example a busy apache2 server, MySQL query is in many situations the bottleneck. So it has to avoid temporary table creation, etc.</li>
  <li>The 3 records must be not contiguous, ie, they must not to be at the vicinity one to another.</li>
  </ol>
  <p>The table has the following fields:</p>
  <pre><code>CREATE TABLE Products (
    ID INT(8) NOT NULL AUTO_INCREMENT,
    Name VARCHAR(255) default NULL,
    HasImages INT default 0,
    ...
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  <p>The WHERE constraint is Products.HasImages=1 permitting to fetch only records that have images available to show on the webpage. About one-third of records meet the condition of  HasImages=1.</p>
  <p><strong>Searching for a Perfection, we first let aside the existent Solutions that have drawbacks:</strong></p>
  <hr />
  <h3>I. <a href="http://davidwalsh.name/mysql-random" rel="noreferrer">This basic solution</a> using ORDER BY RAND(),</h3>
  <p>is too slow but guarantees 3 really random records at each query:</p>
  <pre><code>SELECT ID, Name FROM Products WHERE HasImages=1 ORDER BY RAND() LIMIT 3;
  </code></pre>
  <p>*CPU about 0.10s, scanning 9690 rows because of WHERE clause, Using where; <strong>Using temporary; Using filesort</strong>, on Debian Squeeze Double-Core Linux box, not so bad but</p>
  <p>not so scalable to a bigger table as temporary table and filesort are used, and takes me 8.52s for the first query on the test Windows7::MySQL system. With such a poor performance, to avoid for a webpage isn't-it ?</p>
  <hr />
  <h3>II. The bright solution of <a href="https://stackoverflow.com/users/502502/riedsio">riedsio</a> using JOIN ... RAND(),</h3>
  <p>from <a href="https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast">MySQL select 10 random rows from 600K rows fast</a>, adapted here is only valid for a single random record, as the following query results in an almost always <strong>contiguous</strong> records. In effect it gets only a <strong>random set</strong> of 3 continuous records in IDs:</p>
  <pre><code>SELECT Products.ID, Products.Name
  FROM Products
  INNER JOIN (SELECT (RAND() * (SELECT MAX(ID) FROM Products)) AS ID)
    AS t ON Products.ID &gt;= t.ID
  WHERE (Products.HasImages=1)
  ORDER BY Products.ID ASC
  LIMIT 3;
  </code></pre>
  <p>*CPU about 0.01 - 0.19s, scanning 3200, 9690, 12000 rows or so randomly, but mostly 9690 records, Using where.</p>
  <hr />
  <h3>III. The best solution seems the following with WHERE ... RAND(),</h3>
  <p>seen on <a href="https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast">MySQL select 10 random rows from 600K rows fast</a> proposed by <a href="https://stackoverflow.com/users/943768/bernardo-siu">bernardo-siu</a>:</p>
  <pre><code>SELECT Products.ID, Products.Name FROM Products
  WHERE ((Products.Hasimages=1) AND RAND() &lt; 16 * 3/30000) LIMIT 3;
  </code></pre>
  <p>*CPU about 0.01 - 0.03s, scanning 9690 rows, Using where.</p>
  <p>Here 3 is the number of wished rows, 30000 is the RecordCount of the table Products, 16 is the experimental coefficient to enlarge the selection in order to warrant the 3 records selection. I don't know on what basis the factor <strong>16</strong> is an acceptable approximation.</p>
  <p>We so get at the majority of cases 3 random records and it's very quick, but it's not warranted: sometimes the query returns only 2 rows, sometimes even no record at all.</p>
  <p>The three above methods scan all records of the table meeting WHERE clause, here 9690 rows.</p>
  <h3>A better SQL String?</h3>