MySQL Select Random Records
Sometimes we want to fetch random records from the database table. For example, our table has stored several quotes, and there is a need to display a random quote on GUI. In such a case, we will write an SQL query to fetch random records from the table. In this section, we are going to see how to select a random record from a table.
The following is a syntax to select random records from a database table:
Let us understands the parameters of the statement in detail:
Let us understand how we can generate random records from the database table through an example. First, we will create a table named 'students' using the below statement:
Next, we will fill records into this table using the INSERT statement as follows:
Next, we will display all records from the table using the query is as follows:
Now, we will execute the below query to select random records from the table. Suppose we want to select five random records from the table; we will query the data as follows:
It returns the following output:
If we run the above query again, we will get the output as follows:
It is to notice that whenever we will perform RAND() function, it always returns a different result because it is random. Therefore, this technique works effectively only with a small table. In the case of a big table, it will be slow. It is because MySQL first sorts the entire table and then return the random ones.
The query speed also depends on the number of rows available in the table. Thus if our table has more rows, it takes more time to generate the random records for each row.