Randomly Selecting Rows with MySQL

Question!

To randomly select records from one table; do I have to always set a temporary variable in PHP? I need some help with selecting random rows within a CodeIgniter model, and then display three different ones in a view every time my homepage is viewed. Does anyone have any thoughts on how to solve this issue? Thanks in advance!



Answers

you need a query like this:

SELECT * 
FROM tablename
WHERE somefield='something'
ORDER BY RAND() LIMIT 3

It is taken from the second result of http://www.google.com/search?q=mysql+random and it should work ;)

By : Eineki


Ordering a big table by rand() can be very expensive if the table is very large. MySQL will need to build a temporary table and sort it. If you have primary key and you know how many rows are in the table, use LIMIT x,1 to grab a random row, where x is the number of the row you want to get.



I have this piece of code in production to get a random quote. Using MySQL's RAND function was super slow. Even with 100 quotes in the database, I was noticing a lag time on the website. With this, there was no lag at all.

$result = mysql_query('SELECT COUNT(*) FROM quotes');
$count = mysql_fetch_row($result);
$id = rand(1, $count[0]);
$result = mysql_query("SELECT author, quote FROM quotes WHERE id=$id");
By : Seth


This video can help you solving your question :)
By: admin