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

2 files changed, 50 insertions, 15 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']) )