rand() faster mysql php function

Question

Hi How do I select wallpapers randomly, but cache the last selected one for 10 seconds (for performance reasons)?

the faster RAND() function use Cache in wallpapers or image i use this but i need to but cache in image timely change after 1 mins or 5 mins to chnage images in RAND() randoms wallpapers

i use this :

$sql_wallpaper = "SELECT SQL_CACHE * FROM `wallpaper` WHERE wallpaperid >= (SELECT FLOOR( MAX(wallpaperid) * RAND()) FROM `wallpaper` ) ORDER BY wallpaperid LIMIT 0,7";

but i think its not faster ... Need to use cache help me

Answer 1

a) SQL_CACHE will be ignored, if the query contains RAND()!

b) You will need to save your random id somewhere, e.g. in a database or in apc user data. If using a database you will need to save a timestamp with it, which denotes since then this id is already in use, so you may change it every 5 mins. If using the ladder simply specify a ttl of 5 mins.

Answer 2

Assuming PDO & Memcached:

 $pdo; //is the PDO database instance;
 $memcached;//is the memcached instance;

 function _getThe7Wallpapers(){
      global $memcached;
      $cached = $memcached->get('my7wallpapers');
      if($cached!==false) return $cached;
      global $pdo;
      $pdo->query('SELECT COUNT(*) FROM d INTO @count;');
      $pdo->query('SET @stmt = CONCAT(\'SELECT * FROM d ORDER BY id LIMIT \',ROUND(RAND()*GREATEST(@count-7,0)),\',7\');');
      $pdo->query('PREPARE rander FROM @stmt;');
      $rows = $pdo->query('EXECUTE rander;')->fetchAll(PDO::FETCH_ASSOC);
      $memcached->set('my7wallpapers',$rows,300);//cache for 5 minutes
      return $rows;
 }

How to actually set up a PDO instance (or other db-lib) and memcached can be read in the excellent documentation, so I'll leave that up to you as an exercise.

Answer 3

simply ORDER BY RAND() in your sql - SELECT * FROM wallpaper ORDER BY RAND() LIMIT 0,7 then cache the results on the PHP side for X time using any of the described methods from other answers