Best way to get a random element in MySQL Database

Best way to get a random element in MySQL Database - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Best way to get a random element in MySQL Database (/showthread.php?tid=8216)

Best way to get a random element in MySQL Database - El Forum - 05-09-2008

[eluser]Référencement Google[/eluser]
Hi all,

What would be the best way, considering performances, to get some random results from a MySQL database. I've listen that using of the RAND() attribute make things slow, what's the alternative then?

Best way to get a random element in MySQL Database - El Forum - 05-09-2008

[eluser]xwero[/eluser]
Here is a similar question discussed. I was thinking the same as response 4.

Best way to get a random element in MySQL Database - El Forum - 05-09-2008

[eluser]GSV Sleeper Service[/eluser]
yep, the 'limit' method looks like a good idea.

Best way to get a random element in MySQL Database - El Forum - 05-09-2008

[eluser]Rick Jolly[/eluser]
[quote author="xwero" date="1210363268"]Here is a similar question discussed. I was thinking the same as response 4.[/quote]
I'd be shocked if 2 queries and a php rand() call were faster than 1 query with a mysql rand() call. Performance bottlenecks are usually related to serialization, so 2 calls to the database to unserialize data would probably be slower.

Best way to get a random element in MySQL Database - El Forum - 05-09-2008

[eluser]Référencement Google[/eluser]
@Xwero: Is that a solution to query only 1 result or am I wrong?
(I should have tell that I want 4 random rows)

I have found a query that should do what I want, I just don't know how to do it with Active Record, can you help me?

Code:
SELECT * FROM gallery 

WHERE picture_ID >= (SELECT FLOOR(MAX(picture_ID) * RAND()) FROM gallery ) 

ORDER BY picture_ID LIMIT 4

Best way to get a random element in MySQL Database - El Forum - 05-09-2008

[eluser]xwero[/eluser]
From that discussion i think you can this rule of thumb

Quote:i think we should do it case by case, if your database is small, mustapha code can be implemented. otherwise use PHP random code.

They are speaking about tables with more than a million rows.

I think the only way to do this with AR would be

Code:
$query = $this->db->where('picture_ID >= (SELECT FLOOR(MAX(picture_ID) * RAND()) FROM gallery )')->orderby('picture_ID')->limit(4)->get('gallery');

You are working with a subquery and that isn't introduced yet in the AR library.

Best way to get a random element in MySQL Database - El Forum - 05-09-2008

[eluser]Référencement Google[/eluser]
Hey thanks Xwero, that worked perfectly!

Best way to get a random element in MySQL Database - El Forum - 05-09-2008

[eluser]xwero[/eluser]
of course :coolsmile: