Changeset 2327

Timestamp:

May 3, 2008, 3:52:08 AM (16 years ago)

Author:

rvelices

Message:

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

Location:

trunk

Files:

• include/category_cats.inc.php (modified) (5 diffs)
• include/section_init.inc.php (modified) (2 diffs)
• picture.php (modified) (3 diffs)

trunk/include/category_cats.inc.php

@@ -55 +55 @@
   $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.'
@@ -94 +94 @@
     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
@@ -103 +103 @@
         'visible_images' => 'image_id'
       ),
-    'AND'
+    "\n  AND"
   ).'
   ORDER BY RAND()
@@ -120 +120 @@
   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
@@ -128 +128 @@
         'visible_categories' => 'id',
       ),
-    'AND'
+    "\n  AND"
   ).'
   ORDER BY RAND()

trunk/include/section_init.inc.php

@@ -234 +234 @@
     {// 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
@@ -503 +522 @@
 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)
-    {
-      list($page['image_id'], $page['image_file']) = mysql_fetch_row($result);
+    switch (mysql_num_rows($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;
+          }
+        }
     }
   }

trunk/picture.php

@@ -36 +36 @@
 }
 
+$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(
@@ -100 +102 @@
 // |                            initialization                             |
 // +-----------------------------------------------------------------------+
-
-$page['rank_of'] = array_flip($page['items']);
 
 // caching first_rank, last_rank, current_rank in the displayed
@@ -868 +868 @@
   include(PHPWG_ROOT_PATH.'include/picture_metadata.inc.php');
 }
-//------------------------------------------------------------ log informations
-pwg_log($picture['current']['id'], 'picture');
 
 include(PHPWG_ROOT_PATH.'include/page_header.php');
 trigger_action('loc_end_picture');
 $template->pparse('picture');
+//------------------------------------------------------------ log informations
+pwg_log($picture['current']['id'], 'picture');
 include(PHPWG_ROOT_PATH.'include/page_tail.php');
 ?>