Page 1 of 1

Which RAND() method works FASTEST in MYSQL

Posted: Fri Jun 06, 2008 9:20 am
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:

Re: Which RAND() method works FASTEST in MYSQL

Posted: Fri Jun 06, 2008 9:50 am
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.

Re: Which RAND() method works FASTEST in MYSQL

Posted: Fri Jun 06, 2008 9:53 am
by gosu
How can i try them since i got 10 records in the table ?

Re: Which RAND() method works FASTEST in MYSQL

Posted: Fri Jun 06, 2008 10:11 am
by pickle
Make a bigger table then - "for" loops work well.

Re: Which RAND() method works FASTEST in MYSQL

Posted: Fri Jun 06, 2008 1:08 pm
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:

Re: Which RAND() method works FASTEST in MYSQL

Posted: Fri Jun 06, 2008 2:12 pm
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 :) ).

Re: Which RAND() method works FASTEST in MYSQL

Posted: Sat Jun 07, 2008 3:02 am
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!