Changeset 8726


Ignore:
Timestamp:
Jan 17, 2011, 10:16:42 PM (9 years ago)
Author:
rvelices
Message:

bug 2105 : Browsing tags is slow if tags contains many photos

Location:
trunk/include
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • trunk/include/functions_search.inc.php

    r8611 r8726  
    232232      $query .= "\n  AND ".$images_where;
    233233    }
    234     if (empty($tag_items) or $search['mode']=='AND')
    235     { // directly use forbidden and order by
    236       $query .= $forbidden.'
     234    $query .= $forbidden.'
    237235  '.$conf['order_by'];
    238     }
    239236    $items = array_from_query($query, 'id');
    240237  }
     
    242239  if ( !empty($tag_items) )
    243240  {
    244     $need_permission_check = false;
    245241    switch ($search['mode'])
    246242    {
     
    248244        if (empty($search_clause))
    249245        {
    250           $need_permission_check = true;
    251246          $items = $tag_items;
    252247        }
     
    264259            )
    265260          );
    266         if ( $before_count < count($items) )
    267         {
    268           $need_permission_check = true;
    269         }
    270261        break;
    271     }
    272     if ($need_permission_check and count($items) )
    273     {
    274       $query = '
    275 SELECT DISTINCT(id)
    276   FROM '.IMAGES_TABLE.' i
    277     INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id
    278   WHERE id IN ('.implode(',', $items).') '.$forbidden;
    279       if (!empty($images_where))
    280       {
    281         $query .= "\n  AND ".$images_where;
    282       }
    283       $query .= '
    284   '.$conf['order_by'];
    285       $items = array_from_query($query, 'id');
    286262    }
    287263  }
  • trunk/include/functions_tag.inc.php

    r6652 r8726  
    161161 * @param array tag ids
    162162 * @param string mode
    163  * @return array
    164  */
    165 function get_image_ids_for_tags($tag_ids, $mode = 'AND')
    166 {
    167   switch ($mode)
    168   {
    169     case 'AND':
    170     {
    171       // strategy is to list images associated to each tag
    172       $tag_images = array();
    173 
    174       foreach ($tag_ids as $tag_id)
    175       {
    176         $query = '
    177 SELECT image_id
    178   FROM '.IMAGE_TAG_TABLE.'
    179   WHERE tag_id = '.$tag_id.'
    180 ;';
    181         $tag_images[$tag_id] = array_from_query($query, 'image_id');
    182       }
    183 
    184       // then we calculate the intersection, the images that are associated to
    185       // every tags
    186       $items = array_shift($tag_images);
    187       foreach ($tag_images as $images)
    188       {
    189         $items = array_intersect($items, $images);
    190       }
    191       return $items;
    192       break;
    193     }
    194     case 'OR':
    195     {
    196       $query = '
    197 SELECT DISTINCT image_id
    198   FROM '.IMAGE_TAG_TABLE.'
    199   WHERE tag_id IN ('.implode(',', $tag_ids).')
    200 ;';
    201       return array_from_query($query, 'image_id');
    202       break;
    203     }
    204     default:
    205     {
    206       die('get_image_ids_for_tags: unknown mode, only AND & OR are supported');
    207     }
    208   }
     163 * @param string extra_images_where_sql - optionally apply a sql where filter to retrieved images
     164 * @param string order_by - optionally overwrite default photo order
     165 * @return array
     166 */
     167function get_image_ids_for_tags($tag_ids, $mode='AND', $extra_images_where_sql='', $order_by='')
     168{
     169  global $conf;
     170  if (empty($tag_ids))
     171  {
     172    return array();
     173  }
     174
     175  $query = 'SELECT id
     176  FROM '.IMAGES_TABLE.' i
     177    INNER JOIN '.IMAGE_CATEGORY_TABLE.' ic ON id=ic.image_id
     178    INNER JOIN '.IMAGE_TAG_TABLE.' it ON id=it.image_id
     179    WHERE tag_id IN ('.implode(',', $tag_ids).')'
     180    .get_sql_condition_FandF
     181    (
     182      array
     183        (
     184          'forbidden_categories' => 'category_id',
     185          'visible_categories' => 'category_id',
     186          'visible_images' => 'id'
     187        ),
     188      "\n  AND"
     189    )
     190  .(empty($extra_images_where_sql) ? '' : " \nAND (".$extra_images_where_sql.')')
     191  .'
     192  GROUP BY id';
     193 
     194  if ($mode=='AND' and count($tag_ids)>1)
     195  {
     196    $query .= '
     197  HAVING COUNT(DISTINCT tag_id)='.count($tag_ids);
     198  }
     199  $query .= "\n".(empty($order_by) ? $conf['order_by'] : $order_by);
     200
     201  return array_from_query($query, 'id');
    209202}
    210203
  • trunk/include/section_init.inc.php

    r8665 r8726  
    322322    $items = get_image_ids_for_tags($page['tag_ids']);
    323323
    324     // permissions depends on category, so to only keep images that are
    325     // reachable to the connected user, we need to check category
    326     // associations
    327     if (!empty($items) )
    328     {
    329       $query = '
    330 SELECT DISTINCT image_id
    331   FROM '.IMAGE_CATEGORY_TABLE.' INNER JOIN '.IMAGES_TABLE.' ON image_id=id
    332   WHERE image_id IN ('.implode(',', $items).')
    333     '.$forbidden.
    334     $conf['order_by'].'
    335 ;';
    336       $items =  array_from_query($query, 'image_id');
    337     }
    338 
    339324    $page = array_merge(
    340325      $page,
  • trunk/include/ws_functions.inc.php

    r8464 r8726  
    15591559
    15601560
    1561   $image_ids = array();
     1561  $where_clauses = ws_std_image_sql_filter($params);
     1562  if (!empty($where_clauses))
     1563  {
     1564    $where_clauses = implode( ' AND ', $where_clauses);
     1565  }
     1566  $image_ids = get_image_ids_for_tags(
     1567    $tag_ids,
     1568    $params['tag_mode_and'] ? 'AND' : 'OR',
     1569    $where_clauses,
     1570    ws_std_image_sql_order($params) );
     1571
     1572
     1573  $image_ids = array_slice($image_ids, (int)($params['per_page']*$params['page']), (int)$params['per_page'] );
     1574 
    15621575  $image_tag_map = array();
    1563 
    1564   if ( !empty($tag_ids) )
     1576  if ( !empty($image_ids) and !$params['tag_mode_and'] )
    15651577  { // build list of image ids with associated tags per image
    1566     if ($params['tag_mode_and'])
    1567     {
    1568       $image_ids = get_image_ids_for_tags( $tag_ids );
    1569     }
    1570     else
    1571     {
    1572       $query = '
     1578    $query = '
    15731579SELECT image_id, GROUP_CONCAT(tag_id) AS tag_ids
    15741580  FROM '.IMAGE_TAG_TABLE.'
    1575   WHERE tag_id IN ('.implode(',',$tag_ids).')
     1581  WHERE tag_id IN ('.implode(',',$tag_ids).') AND image_id IN ('.implode(',',$image_ids).')
    15761582  GROUP BY image_id';
    1577       $result = pwg_query($query);
    1578       while ( $row=pwg_db_fetch_assoc($result) )
    1579       {
    1580         $row['image_id'] = (int)$row['image_id'];
    1581         array_push( $image_ids, $row['image_id'] );
    1582         $image_tag_map[ $row['image_id'] ] = explode(',', $row['tag_ids']);
    1583       }
     1583    $result = pwg_query($query);
     1584    while ( $row=pwg_db_fetch_assoc($result) )
     1585    {
     1586      $row['image_id'] = (int)$row['image_id'];
     1587      array_push( $image_ids, $row['image_id'] );
     1588      $image_tag_map[ $row['image_id'] ] = explode(',', $row['tag_ids']);
    15841589    }
    15851590  }
    15861591
    15871592  $images = array();
    1588   if ( !empty($image_ids))
    1589   {
    1590     $where_clauses = ws_std_image_sql_filter($params);
    1591     $where_clauses[] = get_sql_condition_FandF(
    1592         array
    1593           (
    1594             'forbidden_categories' => 'category_id',
    1595             'visible_categories' => 'category_id',
    1596             'visible_images' => 'i.id'
    1597           ),
    1598         '', true
    1599       );
    1600     $where_clauses[] = 'id IN ('.implode(',',$image_ids).')';
    1601 
    1602     $order_by = ws_std_image_sql_order($params);
    1603     if (empty($order_by))
    1604     {
    1605       $order_by = $conf['order_by'];
    1606     }
    1607     else
    1608     {
    1609       $order_by = 'ORDER BY '.$order_by;
    1610     }
    1611 
    1612     $query = '
    1613 SELECT DISTINCT i.* FROM '.IMAGES_TABLE.' i
    1614   INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON i.id=image_id
    1615   WHERE '. implode('
    1616     AND ', $where_clauses).'
    1617 '.$order_by.'
    1618 LIMIT '.(int)$params['per_page'].' OFFSET '.(int)($params['per_page']*$params['page']);
    1619 
    1620     $result = pwg_query($query);
     1593  if (!empty($image_ids))
     1594  {
     1595    $rank_of = array_flip($image_ids);
     1596    $result = pwg_query('
     1597SELECT * FROM '.IMAGES_TABLE.'
     1598  WHERE id IN ('.implode(',',$image_ids).')');
    16211599    while ($row = pwg_db_fetch_assoc($result))
    16221600    {
    16231601      $image = array();
     1602      $image['rank'] = $rank_of[ $row['id'] ];
    16241603      foreach ( array('id', 'width', 'height', 'hit') as $k )
    16251604      {
     
    16651644      array_push($images, $image);
    16661645    }
     1646    usort($images, 'rank_compare');
     1647    unset($rank_of);
    16671648  }
    16681649
     
    25452526  global $template;
    25462527 
    2547   if (!is_admin() || is_adviser() )
     2528  if (!is_admin())
    25482529  {
    25492530    return new PwgError(401, 'Access denied');
Note: See TracChangeset for help on using the changeset viewer.