Ignore:
Timestamp:
04/21/14 15:04:39 (6 years ago)
Author:
plg
Message:

strong speed performance improvement on Random Picture for big galleries (10k+ photos), use 5-minute buffer as cache

File:
1 edited

Legend:

Unmodified
Added
Removed
  • extensions/AMenuManager/amm_pip.class.inc.php

    r21511 r28241  
    409409    $sql=array(); 
    410410 
    411     $sql['select']="SELECT i.id as image_id, i.file as image_file, i.comment, i.path, c.id as catid, c.name, c.permalink, RAND() as rndvalue, i.name as imgname "; 
    412     $sql['from']="FROM ".CATEGORIES_TABLE." c, ".IMAGES_TABLE." i, ".IMAGE_CATEGORY_TABLE." ic "; 
    413     $sql['where']="WHERE c.id = ic.category_id 
    414             AND ic.image_id = i.id 
    415             AND i.level <= ".$user['level']." "; 
     411    // because ORDER BY RAND() can be very slow on a big database, let's 
     412    // make a first query with no join and by security take 5 times 
     413    // $num. We keep the result in session for 5 minutes. 
     414    if (!isset($_SESSION['amm_random_pics']) 
     415        or !isset($_SESSION['amm_random_pics_generated_on']) 
     416        or $_SESSION['amm_random_pics_generated_on'] < time() - 5*60) // 5 minutes ago 
     417    { 
     418      $query = ' 
     419SELECT id 
     420  FROM '.IMAGES_TABLE.' 
     421  WHERE level <= '.$user['level'].' 
     422  ORDER BY RAND() LIMIT '.($num*5).' 
     423;'; 
     424      $_SESSION['amm_random_pics'] = query2array($query, null, 'id'); 
     425      $_SESSION['amm_random_pics_generated_on'] = time(); 
     426    } 
     427     
     428    $sql['select'] = ' 
     429SELECT 
     430    i.id as image_id, 
     431    i.file as image_file, 
     432    i.comment, 
     433    i.path, 
     434    c.id as catid, 
     435    c.name, 
     436    c.permalink, 
     437    i.name as imgname 
     438'; 
     439     
     440    $sql['from'] = ' 
     441  FROM '.CATEGORIES_TABLE.' c 
     442    JOIN '.IMAGE_CATEGORY_TABLE.' ic ON ic.category_id = c.id 
     443    JOIN '.IMAGES_TABLE.' i ON i.id = ic.image_id 
     444'; 
     445     
     446    $sql['where'] = ' 
     447  WHERE i.id IN ('.implode(',', $_SESSION['amm_random_pics']).') 
     448    AND i.level <= '.$user['level'].' 
     449'; 
    416450 
    417451    if($user['forbidden_categories']!="") 
     
    438472    } 
    439473 
    440     $sql=$sql['select'].$sql['from'].$sql['where']." ORDER BY rndvalue LIMIT 0,$num"; 
    441  
     474    $sql = $sql['select'].$sql['from'].$sql['where']." ORDER BY RAND() LIMIT $num;"; 
    442475 
    443476    $result = pwg_query($sql); 
Note: See TracChangeset for help on using the changeset viewer.