rand() Performance concerns?

variaas · 2006-11-07 15:43:22

I’m currently building a plugin that requires the ability to pull a random row or rows from the database. In researching the use of the rand() function, I came across a site that outlined the performance of the function for large datasets.

100 1,000 10,000 100,000 1,000,000
0:00.718s 0:02.092s 0:18.684s 2:59.081s 58:20.000s

This basically states that if you had a table of 1,000 rows and wanted 1 random article from the entire table, it would take 2.092 secs. Is this performance expectation accurate? If so, does some one recommend a solution that would provide for better scalability?

ruud · 2006-11-07 16:43:37

I assume this is the page where you found those numbers. It offers two alternatives. Since letting the database do the work requires subselects which are not supported in MySQL 3.x (not sure about 4.x), you may want to take the first alternative (move work to the application).

Do you have a WHERE part in your query?

Keep in mind that those performance numbers were calculated by performing a query 1000 times, so a single query should take only 1/1000th of the time listed there.

Last edited by ruud (2006-11-07 16:50:43)

variaas · 2006-11-07 20:51:52

Thanks for the insight ruud. I actually didn’t realize that the query was run 1000 times. I guess then in regards to performance running the query once wouldn’t have a significant impact on performance.

Textpattern CMS

Textpattern CMS support forum

#1 2006-11-07 15:43:22

rand() Performance concerns?

#2 2006-11-07 16:43:37

Re: rand() Performance concerns?

#3 2006-11-07 20:51:52

Re: rand() Performance concerns?

Board footer