aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorrvelices <rv-github@modusoptimus.com>2008-05-03 01:51:50 +0000
committerrvelices <rv-github@modusoptimus.com>2008-05-03 01:51:50 +0000
commitbe31d7b2ded3679cf28d1bfac605c9576b034e1d (patch)
treec6b0d1deb55d2af42add502a7ee4b3068c4a7b89
parent226c61c6f9dbd6ebac686b2bf0a5b0ae8eea2238 (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/branches/branch-1_7@2326 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.php6
3 files changed, 53 insertions, 18 deletions
diff --git a/include/category_cats.inc.php b/include/category_cats.inc.php
index 83acade3b..17820a6a0 100644
--- a/include/category_cats.inc.php
+++ b/include/category_cats.inc.php
@@ -57,7 +57,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'].'
@@ -95,8 +95,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
(
@@ -104,7 +104,7 @@ SELECT image_id
'visible_categories' => 'c.id',
'visible_images' => 'image_id'
),
- 'AND'
+ "\n AND"
).'
ORDER BY RAND()
LIMIT 0,1
@@ -121,15 +121,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 f105aaaa8..e2ccacd88 100644
--- a/include/section_init.inc.php
+++ b/include/section_init.inc.php
@@ -237,9 +237,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 0e64d2946..abb3d714b 100644
--- a/picture.php
+++ b/picture.php
@@ -38,9 +38,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',
@@ -103,8 +105,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;