CODEWITHSUNDEEP
phpmysqlfunctionperformancerandom
Hassan
Hassan

Reputation: 43

rand() faster mysql php function

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 change 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 ... It's not using the cache

Upvotes: 1

Views: 1124

Answers (3)

Wrikken
Wrikken

Reputation: 70490

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.

Upvotes: 1

nathan
nathan

Reputation: 5452

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

Upvotes: 0

NikiC
NikiC

Reputation: 101936

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.

Upvotes: 2

Related Questions