Which RAND() method works FASTEST in MYSQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gosu
Forum Newbie
Posts: 19
Joined: Mon Jun 02, 2008 12:17 pm

Which RAND() method works FASTEST in MYSQL

Post by gosu »

Hi guys as we all know

Code: Select all

ORDER BY RAND()
in MYSQL is SLOOOW method if we got huge database.
Which solution you think is best to use as fastest method:
Method 1:

Code: Select all

$db->query("SELECT id, title, date, alt_name, short_story, rating, news_read FROM " . PREFIX . "_post WHERE approve='$conf[allow_index_metka]' AND category regexp '[[:<:]]($conf[cat_num_1])[[:>:]]' AND id >= (SELECT MIN(id) +  ROUND( ( MAX(id)-MIN(id) ) * RAND() ) FROM " . PREFIX . "_post ) ORDER BY id LIMIT 4");
Method 2:

Code: Select all

$db->query("SELECT id, title, date, alt_name, short_story, rating, news_read FROM " . PREFIX . "_post WHERE approve='$conf[allow_index_metka]' AND category regexp '[[:<:]]($conf[cat_num_1])[[:>:]]' AND id >= (SELECT FLOOR( MAX(id) * RAND()) FROM " . PREFIX . "_post ) ORDER BY id LIMIT 4");
or to use something else perhaps? :crazy:
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Which RAND() method works FASTEST in MYSQL

Post by pickle »

Looks like you've spent the time to build the queries - why not try them out?

My guess would be that just doing ORDER BY RAND() would be faster than subqueries, but only tests will tell.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
gosu
Forum Newbie
Posts: 19
Joined: Mon Jun 02, 2008 12:17 pm

Re: Which RAND() method works FASTEST in MYSQL

Post by gosu »

How can i try them since i got 10 records in the table ?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Which RAND() method works FASTEST in MYSQL

Post by pickle »

Make a bigger table then - "for" loops work well.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
gosu
Forum Newbie
Posts: 19
Joined: Mon Jun 02, 2008 12:17 pm

Re: Which RAND() method works FASTEST in MYSQL

Post by gosu »

Actually I found a tool called Datagenerator, http://datagenerator.sourceforge.net/ which is able to
generate records in the tables of the db, i was able to make 200k news, which i will upload to the server
and i will see which works fastest :), Also good to check how the cache, gzip and other stuff of my skript work before the site is heavily loaded with real publications :mrgreen:
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Which RAND() method works FASTEST in MYSQL

Post by onion2k »

Can you post the results of your benchmark here when you're done please. It'd be very handy to know what's best (and I'm much too lazy to do it myself :) ).
gosu
Forum Newbie
Posts: 19
Joined: Mon Jun 02, 2008 12:17 pm

Re: Which RAND() method works FASTEST in MYSQL

Post by gosu »

The article that gave me idea to change RAND() with something else:
http://akinas.com/pages/en/blog/mysql_random_row/
I decided to use Method 4 located in the article which should be second to execute fastest.

5 Articles @ 1000 Pages = 5000 Articles, DB size 15MB

Image

Main page, the rand() is there

Image

The software that I used for generation of the test data

Image

And the results:

no table records, cache system turned OFF
Page generated in 0.05536 seconds with 21 queries

no table records, cache system turned ON
Page generated in 0.02746 seconds with 14 queries


5000 records, cache system turned ON, RAND()
Page generated in 0.02609 seconds with 1 queries

5000 records, cache system turned OFF, RAND()
Page generated in 0.55332 seconds with 21 queries


And the amazing good results using Method 1:

5000 records, cache system turned OFF, Method 1
Page generated in 32.4499 seconds with 21 queries

5000 records, cache system turned ON, Method 1:
Page generated in 0.02587 seconds with 1 queries
Maximum generation time here was again ~30 sec @ 20 queries
when the cache is empty


5000 records, cache system turned OFF, Method 2:
Page generated in 33.72876 seconds with 33 queries

5000 records, cache system turned ON, Method 2:
Page generated in 0.02655 seconds with 1 queries
Maximum generation time here was again ~30 sec @ 30 queries
when the cache is empty


As we can see these methods are UNSPEAKABLE EVIL.
I also did a test with 150 000 articles on my PC
RAND() method had around 4 sec execution time, and
the other 2 methods didn't even work!
Post Reply