Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Ugly, but quick and random. Can become very ugly very fast, especially with tuning described below, so make sure you really want it this way.</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 LIMIT 1) UNION ALL (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 LIMIT 1) UNION ALL (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 LIMIT 1) </code></pre> <h1>First row appears more often than it should</h1> <p>If you have big gaps between IDs in your table, rows right after such gaps will have bigger chance to be fetched by this query. In some cases, they will appear significatnly more often than they should. This can not be solved in general, but there's a fix for a common particular case: when there's a gap between 0 and the first existing ID in a table.</p> <p>Instead of subquery <code>(SELECT RAND()*&lt;max_id&gt; AS ID)</code> use something like <code>(SELECT &lt;min_id&gt; + RAND()*(&lt;max_id&gt; - &lt;min_id&gt;) AS ID)</code></p> <h1>Remove duplicates</h1> <p>The query, if used as is, may return duplicate rows. It is possible to avoid that by using <code>UNION</code> instead of <code>UNION ALL</code>. This way duplicates will be merged, but the query no longer guarantees to return exactly 3 rows. You can work around that too, by fetching more rows than you need and limiting the outer result like this:</p> <pre><code>(SELECT ... LIMIT 1) UNION (SELECT ... LIMIT 1) UNION (SELECT ... LIMIT 1) ... UNION (SELECT ... LIMIT 1) LIMIT 3 </code></pre> <p>There's still no guarantee that 3 rows will be fetched, though. It just makes it more likely.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to quickly SELECT 3 random records from a 30k MySQL table with a where filter by a single query?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USLeo Nyx
    1. USLeo Nyx
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to quickly SELECT 3 random records from a 30k MySQL table with a where filter by a single query?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThis solution assures really 3 times of one random row. The drawback is that the three ID's can be mathematically the same, eg, 10088, 10088, 10088. But this may never occur in the real world. It uses up CPU 0.01 to 0.20s.
      singulars
      1. PO
      1. USjacouh
    2. CO@jacouh do you have an index on `(HasImages)`? This should be much faster than 0.20seconds.
      singulars
      1. PO
      1. USypercubeᵀᴹ
    3. CO@ypercube, yes this table has PK .ID and .HasImages is also indexed.
      singulars
      1. PO
      1. USjacouh
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload