StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I used this <a href="http://jan.kneschke.de/projects/mysql/order-by-rand/" rel="nofollow">http://jan.kneschke.de/projects/mysql/order-by-rand/</a> posted by Riedsio (i used the case of a stored procedure that returns one or more random values):</p> <pre><code> DROP TEMPORARY TABLE IF EXISTS rands; CREATE TEMPORARY TABLE rands ( rand_id INT ); loop_me: LOOP IF cnt < 1 THEN LEAVE loop_me; END IF; INSERT INTO rands SELECT r1.id FROM random AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM random)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1; SET cnt = cnt - 1; END LOOP loop_me; </code></pre> <p>In the article he solves the <strong>problem of gaps</strong> in ids causing <strong>not so random results</strong> by maintaining a table (using triggers, etc...see the article); I'm solving the problem by adding another column to the table, populated with contiguous numbers, starting from 1 (<strong><em>edit:</em></strong> this column is added to the temporary table created by the subquery at runtime, doesn't affect your permanent table):</p> <pre><code> DROP TEMPORARY TABLE IF EXISTS rands; CREATE TEMPORARY TABLE rands ( rand_id INT ); loop_me: LOOP IF cnt < 1 THEN LEAVE loop_me; END IF; SET @no_gaps_id := 0; INSERT INTO rands SELECT r1.id FROM (SELECT id, @no_gaps_id := @no_gaps_id + 1 AS no_gaps_id FROM random) AS r1 JOIN (SELECT (RAND() * (SELECT COUNT(*) FROM random)) AS id) AS r2 WHERE r1.no_gaps_id >= r2.id ORDER BY r1.no_gaps_id ASC LIMIT 1; SET cnt = cnt - 1; END LOOP loop_me; </code></pre> <p>In the article i can see he went to great lengths to optimize the code; i have no ideea if/how much my changes impact the performance but works very well for me.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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