Most Efficient Way To Retrieve MYSQL data in random order PHP

Question

Hey, I have a query like so

SELECT * FROM table WHERE premium = 3

I'm just wondering how I can have it return a random order for the results!

i was told not to use ORDER BY RAND(); because of the amount of system resources it uses.

Thanks.

Answer 1

How many results are you expecting? If it's small, why not just grab all of the results and then select random indices from them?

Answer 2

Sort in random order in PHP instead of MySQL

I read from the scalability blog that Digg got an improve of 4000% sorting in PHP instead of Mysql. So my advice would be to do the sorting in PHP instead of Mysql. I guess you could put the results in an array which you shuffle.

When implementing the comment feature a 4,000 percent increase in performance was created by sorting in PHP instead of MySQL

Store results in APC(memory)

Instead of fetching the results from the database every time you should try to cache them using for example APC(in memory which is lightning fast) to minimize the database load. Update the cache when SELECT * FROM table WHERE premium = 3 has changed. APC is a must anyway for any big site because compiling PHP code consumes (a lot of) time.

If you can't compile APC then your site will never run as fast as it could. You could store the database fetch in a file, but then you have to do disc IO which can hurt you a lot.

App engine

If you can't compile APC(to be honest I would always have a look at google's app engine) then I would advise you to have a look at google's app engine because they have memcached which is an in memory database(lightning fast). You can even run PHP code via quercus on app engine. Google has a generous free quota and is cheap once you use the free quota.