aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorrvelices <rv-github@modusoptimus.com>2008-05-03 01:52:08 +0000
committerrvelices <rv-github@modusoptimus.com>2008-05-03 01:52:08 +0000
commit40a882c3cf93030546f5ae2eddde7644c9db99bc (patch)
tree3be039a6b696c86421aa66b00318c344e62e9d2d
parent5436a351ef6dd685483f022af6656bbf8b797cb6 (diff)
just some optimizations (especially for large dbs)
- replace some REGEXP with LIKE in sql - optimized queries for the combination of large data sets with picture_url_style file git-svn-id: http://piwigo.org/svn/trunk@2327 68402e56-0260-453c-a942-63ccdbb3a9ee
Diffstat (limited to '')
-rw-r--r--include/category_cats.inc.php16
-rw-r--r--include/section_init.inc.php49
-rw-r--r--picture.php10
3 files changed, 55 insertions, 20 deletions
diff --git a/include/category_cats.inc.php b/include/category_cats.inc.php
index 0285be938..f93a64dd1 100644
--- a/include/category_cats.inc.php
+++ b/include/category_cats.inc.php
@@ -54,7 +54,7 @@ else
// $user['forbidden_categories'] including with USER_CACHE_CATEGORIES_TABLE
$query = '
SELECT
- id, name, permalink, representative_picture_id, comment, nb_images,
+ id, name, permalink, representative_picture_id, comment, nb_images, uppercats,
date_last, max_date_last, count_images, count_categories
FROM '.CATEGORIES_TABLE.' INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.'
ON id = cat_id and user_id = '.$user['id'].'
@@ -93,8 +93,8 @@ SELECT image_id
FROM '.CATEGORIES_TABLE.' AS c INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic
ON ic.category_id = c.id';
$query.= '
- WHERE uppercats REGEXP \'(^|,)'.$row['id'].'(,|$)\'
-'.get_sql_condition_FandF
+ WHERE uppercats LIKE \''.$row['uppercats'].',%\''
+ .get_sql_condition_FandF
(
array
(
@@ -102,7 +102,7 @@ SELECT image_id
'visible_categories' => 'c.id',
'visible_images' => 'image_id'
),
- 'AND'
+ "\n AND"
).'
ORDER BY RAND()
LIMIT 0,1
@@ -119,15 +119,15 @@ SELECT image_id
SELECT representative_picture_id
FROM '.CATEGORIES_TABLE.' INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.'
ON id = cat_id and user_id = '.$user['id'].'
- WHERE uppercats REGEXP \'(^|,)'.$row['id'].'(,|$)\'
- AND representative_picture_id IS NOT NULL
-'.get_sql_condition_FandF
+ WHERE uppercats LIKE \''.$row['uppercats'].',%\'
+ AND representative_picture_id IS NOT NULL'
+ .get_sql_condition_FandF
(
array
(
'visible_categories' => 'id',
),
- 'AND'
+ "\n AND"
).'
ORDER BY RAND()
LIMIT 0,1
diff --git a/include/section_init.inc.php b/include/section_init.inc.php
index e204c9769..ffd6cac1a 100644
--- a/include/section_init.inc.php
+++ b/include/section_init.inc.php
@@ -233,9 +233,28 @@ if ('categories' == $page['section'])
if (isset($page['flat']))
{// flat categories mode
if ( isset($page['category']) )
- {
- $subcat_ids = get_subcat_ids( array($page['category']['id']) );
+ { // get all allowed sub-categories
+ $query = '
+SELECT id
+ FROM '.CATEGORIES_TABLE.'
+ WHERE
+ uppercats LIKE "'.$page['category']['uppercats'].',%" '
+ .get_sql_condition_FandF(
+ array
+ (
+ 'forbidden_categories' => 'id',
+ 'visible_categories' => 'id',
+ ),
+ "\n AND"
+ );
+ $subcat_ids = array_from_query($query, 'id');
+ $subcat_ids[] = $page['category']['id'];
$where_sql = 'category_id IN ('.implode(',',$subcat_ids).')';
+ // remove categories from forbidden because just checked above
+ $forbidden = get_sql_condition_FandF(
+ array( 'visible_images' => 'id' ),
+ 'AND'
+ );
}
else
{
@@ -502,13 +521,29 @@ if (script_basename() == 'picture'
$query = '
SELECT id,file
FROM '.IMAGES_TABLE .'
- WHERE id IN ('.implode(',',$page['items']).')
- AND file LIKE "' . $page['image_file'] . '.%" ESCAPE "|"'
-;
+ WHERE file LIKE "' . $page['image_file'] . '.%" ESCAPE "|"';
+ if ( count($page['items']) < 500)
+ {// for very large item sets do not add IN - because slow
+ $query .= '
+ AND id IN ('.implode(',',$page['items']).')
+ LIMIT 0,1';
+ }
$result = pwg_query($query);
- if (mysql_num_rows($result)>0)
+ switch (mysql_num_rows($result))
{
- list($page['image_id'], $page['image_file']) = mysql_fetch_row($result);
+ case 0: break;
+ case 1:
+ list($page['image_id'], $page['image_file']) = mysql_fetch_row($result);
+ break;
+ default: // more than 1 file name match
+ while ($row = mysql_fetch_row($result) )
+ {
+ if ( in_array($row[0], $page['items']) )
+ {
+ list($page['image_id'], $page['image_file']) = $row;
+ break;
+ }
+ }
}
}
if ( !isset($page['image_id']) )
diff --git a/picture.php b/picture.php
index 286b51ff6..8daacb353 100644
--- a/picture.php
+++ b/picture.php
@@ -35,9 +35,11 @@ if (isset($page['category']))
check_restrictions($page['category']['id']);
}
+$page['rank_of'] = array_flip($page['items']);
+
// if this image_id doesn't correspond to this category, an error message is
// displayed, and execution is stopped
-if (!in_array($page['image_id'], $page['items']))
+if ( !isset($page['rank_of'][$page['image_id']]) )
{
page_not_found(
'The requested image does not belong to this image set',
@@ -100,8 +102,6 @@ function default_picture_content($content, $element_info)
// | initialization |
// +-----------------------------------------------------------------------+
-$page['rank_of'] = array_flip($page['items']);
-
// caching first_rank, last_rank, current_rank in the displayed
// section. This should also help in readability.
$page['first_rank'] = 0;
@@ -867,11 +867,11 @@ if ($metadata_showable and isset($_GET['metadata']))
{
include(PHPWG_ROOT_PATH.'include/picture_metadata.inc.php');
}
-//------------------------------------------------------------ log informations
-pwg_log($picture['current']['id'], 'picture');
include(PHPWG_ROOT_PATH.'include/page_header.php');
trigger_action('loc_end_picture');
$template->pparse('picture');
+//------------------------------------------------------------ log informations
+pwg_log($picture['current']['id'], 'picture');
include(PHPWG_ROOT_PATH.'include/page_tail.php');
?>