From 40a882c3cf93030546f5ae2eddde7644c9db99bc Mon Sep 17 00:00:00 2001
From: rvelices <rv-github@modusoptimus.com>
Date: Sat, 3 May 2008 01:52:08 +0000
Subject: 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
---
 include/section_init.inc.php | 49 +++++++++++++++++++++++++++++++++++++-------
 1 file changed, 42 insertions(+), 7 deletions(-)

(limited to 'include/section_init.inc.php')

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']) )
-- 
cgit v1.2.3