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

1 files changed, 8 insertions, 8 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