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/category_cats.inc.php | 16 ++++++++--------
 1 file changed, 8 insertions(+), 8 deletions(-)

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

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