Best way to get a random element in MySQL Database |
[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?
[eluser]GSV Sleeper Service[/eluser]
yep, the 'limit' method looks like a good idea.
[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.
[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
[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');
[eluser]Référencement Google[/eluser]
Hey thanks Xwero, that worked perfectly!
|
Welcome Guest, Not a member yet? Register Sign In |