From e123585dde33f8ec3be13e788eea6fc10041920f Mon Sep 17 00:00:00 2001 From: rvelices Date: Mon, 17 Jan 2011 21:16:42 +0000 Subject: bug 2105 : Browsing tags is slow if tags contains many photos git-svn-id: http://piwigo.org/svn/trunk@8726 68402e56-0260-453c-a942-63ccdbb3a9ee --- include/functions_search.inc.php | 26 +----------- include/functions_tag.inc.php | 73 ++++++++++++++++------------------ include/section_init.inc.php | 15 ------- include/ws_functions.inc.php | 85 ++++++++++++++++------------------------ 4 files changed, 67 insertions(+), 132 deletions(-) diff --git a/include/functions_search.inc.php b/include/functions_search.inc.php index a8b005517..08cd675a2 100644 --- a/include/functions_search.inc.php +++ b/include/functions_search.inc.php @@ -231,23 +231,18 @@ SELECT DISTINCT(id) { $query .= "\n AND ".$images_where; } - if (empty($tag_items) or $search['mode']=='AND') - { // directly use forbidden and order by - $query .= $forbidden.' + $query .= $forbidden.' '.$conf['order_by']; - } $items = array_from_query($query, 'id'); } if ( !empty($tag_items) ) { - $need_permission_check = false; switch ($search['mode']) { case 'AND': if (empty($search_clause)) { - $need_permission_check = true; $items = $tag_items; } else @@ -263,27 +258,8 @@ SELECT DISTINCT(id) $tag_items ) ); - if ( $before_count < count($items) ) - { - $need_permission_check = true; - } break; } - if ($need_permission_check and count($items) ) - { - $query = ' -SELECT DISTINCT(id) - FROM '.IMAGES_TABLE.' i - INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id - WHERE id IN ('.implode(',', $items).') '.$forbidden; - if (!empty($images_where)) - { - $query .= "\n AND ".$images_where; - } - $query .= ' - '.$conf['order_by']; - $items = array_from_query($query, 'id'); - } } return $items; diff --git a/include/functions_tag.inc.php b/include/functions_tag.inc.php index 0f5859333..4228219d4 100644 --- a/include/functions_tag.inc.php +++ b/include/functions_tag.inc.php @@ -160,52 +160,45 @@ function add_level_to_tags($tags) * * @param array tag ids * @param string mode + * @param string extra_images_where_sql - optionally apply a sql where filter to retrieved images + * @param string order_by - optionally overwrite default photo order * @return array */ -function get_image_ids_for_tags($tag_ids, $mode = 'AND') +function get_image_ids_for_tags($tag_ids, $mode='AND', $extra_images_where_sql='', $order_by='') { - switch ($mode) + global $conf; + if (empty($tag_ids)) { - case 'AND': - { - // strategy is to list images associated to each tag - $tag_images = array(); - - foreach ($tag_ids as $tag_id) - { - $query = ' -SELECT image_id - FROM '.IMAGE_TAG_TABLE.' - WHERE tag_id = '.$tag_id.' -;'; - $tag_images[$tag_id] = array_from_query($query, 'image_id'); - } + return array(); + } - // then we calculate the intersection, the images that are associated to - // every tags - $items = array_shift($tag_images); - foreach ($tag_images as $images) - { - $items = array_intersect($items, $images); - } - return $items; - break; - } - case 'OR': - { - $query = ' -SELECT DISTINCT image_id - FROM '.IMAGE_TAG_TABLE.' - WHERE tag_id IN ('.implode(',', $tag_ids).') -;'; - return array_from_query($query, 'image_id'); - break; - } - default: - { - die('get_image_ids_for_tags: unknown mode, only AND & OR are supported'); - } + $query = 'SELECT id + FROM '.IMAGES_TABLE.' i + INNER JOIN '.IMAGE_CATEGORY_TABLE.' ic ON id=ic.image_id + INNER JOIN '.IMAGE_TAG_TABLE.' it ON id=it.image_id + WHERE tag_id IN ('.implode(',', $tag_ids).')' + .get_sql_condition_FandF + ( + array + ( + 'forbidden_categories' => 'category_id', + 'visible_categories' => 'category_id', + 'visible_images' => 'id' + ), + "\n AND" + ) + .(empty($extra_images_where_sql) ? '' : " \nAND (".$extra_images_where_sql.')') + .' + GROUP BY id'; + + if ($mode=='AND' and count($tag_ids)>1) + { + $query .= ' + HAVING COUNT(DISTINCT tag_id)='.count($tag_ids); } + $query .= "\n".(empty($order_by) ? $conf['order_by'] : $order_by); + + return array_from_query($query, 'id'); } /** diff --git a/include/section_init.inc.php b/include/section_init.inc.php index 02b1f83ab..7f0cbf96d 100644 --- a/include/section_init.inc.php +++ b/include/section_init.inc.php @@ -321,21 +321,6 @@ else $items = get_image_ids_for_tags($page['tag_ids']); - // permissions depends on category, so to only keep images that are - // reachable to the connected user, we need to check category - // associations - if (!empty($items) ) - { - $query = ' -SELECT DISTINCT image_id - FROM '.IMAGE_CATEGORY_TABLE.' INNER JOIN '.IMAGES_TABLE.' ON image_id=id - WHERE image_id IN ('.implode(',', $items).') - '.$forbidden. - $conf['order_by'].' -;'; - $items = array_from_query($query, 'image_id'); - } - $page = array_merge( $page, array( diff --git a/include/ws_functions.inc.php b/include/ws_functions.inc.php index 56194f32a..96f59682c 100644 --- a/include/ws_functions.inc.php +++ b/include/ws_functions.inc.php @@ -1558,69 +1558,48 @@ function ws_tags_getImages($params, &$service) $tag_ids = array_keys($tags_by_id); - $image_ids = array(); - $image_tag_map = array(); + $where_clauses = ws_std_image_sql_filter($params); + if (!empty($where_clauses)) + { + $where_clauses = implode( ' AND ', $where_clauses); + } + $image_ids = get_image_ids_for_tags( + $tag_ids, + $params['tag_mode_and'] ? 'AND' : 'OR', + $where_clauses, + ws_std_image_sql_order($params) ); - if ( !empty($tag_ids) ) + + $image_ids = array_slice($image_ids, (int)($params['per_page']*$params['page']), (int)$params['per_page'] ); + + $image_tag_map = array(); + if ( !empty($image_ids) and !$params['tag_mode_and'] ) { // build list of image ids with associated tags per image - if ($params['tag_mode_and']) - { - $image_ids = get_image_ids_for_tags( $tag_ids ); - } - else - { - $query = ' + $query = ' SELECT image_id, GROUP_CONCAT(tag_id) AS tag_ids FROM '.IMAGE_TAG_TABLE.' - WHERE tag_id IN ('.implode(',',$tag_ids).') + WHERE tag_id IN ('.implode(',',$tag_ids).') AND image_id IN ('.implode(',',$image_ids).') GROUP BY image_id'; - $result = pwg_query($query); - while ( $row=pwg_db_fetch_assoc($result) ) - { - $row['image_id'] = (int)$row['image_id']; - array_push( $image_ids, $row['image_id'] ); - $image_tag_map[ $row['image_id'] ] = explode(',', $row['tag_ids']); - } + $result = pwg_query($query); + while ( $row=pwg_db_fetch_assoc($result) ) + { + $row['image_id'] = (int)$row['image_id']; + array_push( $image_ids, $row['image_id'] ); + $image_tag_map[ $row['image_id'] ] = explode(',', $row['tag_ids']); } } $images = array(); - if ( !empty($image_ids)) - { - $where_clauses = ws_std_image_sql_filter($params); - $where_clauses[] = get_sql_condition_FandF( - array - ( - 'forbidden_categories' => 'category_id', - 'visible_categories' => 'category_id', - 'visible_images' => 'i.id' - ), - '', true - ); - $where_clauses[] = 'id IN ('.implode(',',$image_ids).')'; - - $order_by = ws_std_image_sql_order($params); - if (empty($order_by)) - { - $order_by = $conf['order_by']; - } - else - { - $order_by = 'ORDER BY '.$order_by; - } - - $query = ' -SELECT DISTINCT i.* FROM '.IMAGES_TABLE.' i - INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON i.id=image_id - WHERE '. implode(' - AND ', $where_clauses).' -'.$order_by.' -LIMIT '.(int)$params['per_page'].' OFFSET '.(int)($params['per_page']*$params['page']); - - $result = pwg_query($query); + if (!empty($image_ids)) + { + $rank_of = array_flip($image_ids); + $result = pwg_query(' +SELECT * FROM '.IMAGES_TABLE.' + WHERE id IN ('.implode(',',$image_ids).')'); while ($row = pwg_db_fetch_assoc($result)) { $image = array(); + $image['rank'] = $rank_of[ $row['id'] ]; foreach ( array('id', 'width', 'height', 'hit') as $k ) { if (isset($row[$k])) @@ -1664,6 +1643,8 @@ LIMIT '.(int)$params['per_page'].' OFFSET '.(int)($params['per_page']*$params['p ); array_push($images, $image); } + usort($images, 'rank_compare'); + unset($rank_of); } return array( 'images' => @@ -2544,7 +2525,7 @@ function ws_themes_performAction($params, &$service) { global $template; - if (!is_admin() || is_adviser() ) + if (!is_admin()) { return new PwgError(401, 'Access denied'); } -- cgit v1.2.3