diff options
author | rvelices <rv-github@modusoptimus.com> | 2008-05-03 01:51:50 +0000 |
---|---|---|
committer | rvelices <rv-github@modusoptimus.com> | 2008-05-03 01:51:50 +0000 |
commit | be31d7b2ded3679cf28d1bfac605c9576b034e1d (patch) | |
tree | c6b0d1deb55d2af42add502a7ee4b3068c4a7b89 /include | |
parent | 226c61c6f9dbd6ebac686b2bf0a5b0ae8eea2238 (diff) |
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
Diffstat (limited to '')
-rw-r--r-- | include/category_cats.inc.php | 16 | ||||
-rw-r--r-- | include/section_init.inc.php | 49 |
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']) ) |