Скачать или смотреть Get Random Rows in MySQL Without Auto-Increment

Discover how to retrieve random rows efficiently from a MySQL database without using auto-increment, especially when using BINARY(16) identifiers.
---
This video is based on the question https://stackoverflow.com/q/67829512/ asked by the user 'Ricardo' ( https://stackoverflow.com/u/917338/ ) and on the answer https://stackoverflow.com/a/67829698/ provided by the user 'ysth' ( https://stackoverflow.com/u/17389/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How can I get random rows in MySQL (NO autoincrement)?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Retrieve Random Rows in MySQL Without Auto-Increment

When working with large databases, accessing random rows can sometimes seem daunting, particularly if you want to avoid the typical SORT BY RAND() method due to its performance drawbacks. If your database employs BINARY(16) as identifiers or primary keys, such as randomly generated hashes, you may face additional unique challenges. In this post, we will explore an effective solution to retrieve random rows quickly while maintaining reasonable accuracy.

Understanding the Problem: Retrieving Random Rows

If you have a MySQL database that does not use auto-incremented integers for IDs, getting a random selection of rows poses specific difficulties. Traditional methods might not work effectively or at all. Here are the key challenges:

Performance: Using SORT BY RAND() can be extremely slow, especially in large datasets.

Unique Identifiers: Utilizing random identifiers like BINARY(16) removes the ease of accessing items through a predictable integer sequence.

Accuracy: Speed takes precedence over perfect randomness in certain use cases, making the need for a practical solution even more pressing.

Potential Solutions Explored

Several approaches suggest themselves when faced with this challenge. Let’s discuss them briefly, along with their advantages and drawbacks:

Using a Random Column with RAND():

Add an extra column that uses RAND() to sort the rows.

Drawback: This generates the same random rows unless regularly updated, which adds unnecessary complexity.

Two-Step Query Based on Creation Dates:

Perform two requests: first to retrieve the oldest creation date, then select a random date between that and the current date.

Drawback: This method can introduce inaccuracies due to uneven distribution of creation dates.

Leveraging Random IDs:

Use an already random ID and sort starting from a randomly selected bit, ensuring you find the first id greater than or equal to that.

Advantage: This method pairs speed with reasonable randomness, making it suitable for your needs.

The Recommended Approach: Querying with Random Values

A straightforward solution when dealing with random IDs is to select a random value and then find the first ID in the database that is greater than or equal to that value. Here’s how you can implement this query efficiently:

Step 1: Generate a Random Value

Using a combination of SQL functions, you can generate a random 16-byte string:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Explanation of the Query

UNHEX(MD5(RAND())): This generates a random BINARY(16) value using the RAND() function combined with MD5 hashing.

WHERE id >=: This ensures that your query will look for the first ID that is greater than or equal to the randomly generated value, maximizing performance.

ORDER BY id LIMIT 1: This orders the results to only return the first matching ID, minimizing the load on the database.

Conclusion

Retrieving random rows from a MySQL database without relying on auto-incremented identifiers can initially appear complex; however, with effective approaches like the one outlined above, you can achieve a balance of speed and randomness. Testing this solution in your environment can lead to swift access to random data without the performance hits associated with SORT BY RAND().

Consider applying this method to enhance efficiency in your database queries and feel free to experiment with the variations discussed to find the best fit for your specific needs.