rand() faster mysql php function

Question

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

It's hard to determine what you're asking. My best guess is this: "How do I select wallpapers randomly, but cache the last selected one for 5 minutes (for performance reasons)"? — Brendan Long, Jul 23 '10 at 22:00
Related: http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand — OMG Ponies, Jul 23 '10 at 22:05
yes i ask like this images cache and change after 1 min or else use cache in rand() help? — Hassan, Jul 23 '10 at 22:15

NikiC · Answer 1 · 2010-07-23T22:53:17.533

2

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.

edited Jul 23 '10 at 22:53

answered Jul 23 '10 at 22:45

NikiC

100,734
37
191
225

Wrikken · Answer 2 · 2010-07-24T13:55:46.940

1

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.

edited Jul 24 '10 at 13:55

answered Jul 24 '10 at 13:31

Wrikken

69,272
8
97
136

Its great but its Show one Random wallpaper i need to be 7 wallpaper show, how do that? reply must – Hassan Jul 24 '10 at 21:20
i need it to display LIMIT 0,7 7 random thumbs display How? – Hassan Jul 24 '10 at 21:30
It will fetch 7 rows, how and when you alter those rows into some html you like is entirely up to you. The `fetchAll` should return 7 rows of data. – Wrikken Jul 25 '10 at 03:10
its fetchAll but Showing only one record please i need to show 6 or 7 record? how – Hassan Jul 25 '10 at 06:17
@Hassan: I don't know what code does the actual showing, or what coding error you made in those. I'm not here to do the actual work for you. The answer I gave you points you into a possible, it is up to you to debug & adapt it to your needs. – Wrikken Jul 25 '10 at 17:13
ok thanks for help but problem is still finding other way thanks again – Hassan Jul 25 '10 at 20:46

score 0 · Answer 3 · answered Jul 24 '10 at 13:37

0

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

answered Jul 24 '10 at 13:37

nathan

5,402
1
22
18

rand() faster mysql php function

3 Answers3