Welcome Guest, Not a member yet? Register   Sign In
Best way to get a random element in MySQL Database
#1

[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?
#2

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

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

[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.
#5

[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
#6

[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.
#7

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

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




Theme © iAndrew 2016 - Forum software by © MyBB