aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorplegall <plg@piwigo.org>2004-11-21 11:13:48 +0000
committerplegall <plg@piwigo.org>2004-11-21 11:13:48 +0000
commitec015abc782f6e615ea561e11132aa71076cdd04 (patch)
treebcb6d38ccd043bcec51de1529a9e755b408c83f3
parent9f037e7cea37aafd562fa494afd9a289c60a5855 (diff)
optimization : instead of reading each $conf['calendar_datefield'] for
matching YEAR() and MONTH(), use of SQL statement BETWEEN ... AND ... on the date field + new index image_i5 on date_creation (default date field for calendar) git-svn-id: http://piwigo.org/svn/trunk@612 68402e56-0260-453c-a942-63ccdbb3a9ee
-rw-r--r--include/category_calendar.inc.php36
-rw-r--r--install/dbscheme.txt1
-rw-r--r--install/phpwebgallery_structure.sql3
3 files changed, 21 insertions, 19 deletions
diff --git a/include/category_calendar.inc.php b/include/category_calendar.inc.php
index 5c13e940a..b318c7c25 100644
--- a/include/category_calendar.inc.php
+++ b/include/category_calendar.inc.php
@@ -86,10 +86,11 @@ if (isset($page['calendar_year']))
$query = '
SELECT DISTINCT(MONTH('.$conf['calendar_datefield'].')) AS month
, COUNT(id) AS count
- FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
+ FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
'.$page['where'].'
- AND id = image_id
- AND YEAR('.$conf['calendar_datefield'].') = '.$page['calendar_year'].'
+ AND '.$conf['calendar_datefield'].'
+ BETWEEN \''.$page['calendar_year'].'-1-1\'
+ AND \''.$page['calendar_year'].'-12-31\'
GROUP BY MONTH('.$conf['calendar_datefield'].')
;';
$result = pwg_query($query);
@@ -157,11 +158,11 @@ elseif (!isset($page['calendar_day']))
// the number of picture for this day : $calendar_days
$query = '
SELECT DISTINCT('.$conf['calendar_datefield'].') AS day, COUNT(id) AS count
- FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
+ FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
'.$page['where'].'
- AND id = image_id
- AND YEAR('.$conf['calendar_datefield'].') = '.$page['calendar_year'].'
- AND MONTH('.$conf['calendar_datefield'].') = '.$page['calendar_month'].'
+ AND '.$conf['calendar_datefield'].'
+ BETWEEN \''.$page['calendar_year'].'-'.$page['calendar_month'].'-1\'
+ AND \''.$page['calendar_year'].'-'.$page['calendar_month'].'-31\'
GROUP BY day
;';
$result = pwg_query($query);
@@ -182,10 +183,9 @@ elseif (isset($page['calendar_day']))
$query = '
SELECT category_id AS category, COUNT(id) AS count
- FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
+ FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
'.$page['where'].'
AND '.$conf['calendar_datefield'].' = \''.$page['calendar_date'].'\'
- AND id = image_id
GROUP BY category_id
;';
$result = pwg_query($query);
@@ -220,10 +220,11 @@ if (!isset($page['calendar_year']))
{
$query = '
SELECT file,tn_ext,'.$conf['calendar_datefield'].',path
- FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
+ FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
'.$page['where'].'
- AND YEAR('.$conf['calendar_datefield'].') = '.$calendar_year.'
- AND id = image_id
+ AND '.$conf['calendar_datefield'].'
+ BETWEEN \''.$calendar_year.'-1-1\'
+ AND \''.$calendar_year.'-12-31\'
ORDER BY RAND()
LIMIT 0,1
;';
@@ -265,11 +266,11 @@ elseif (!isset($page['calendar_month']))
{
$query = '
SELECT file,tn_ext,'.$conf['calendar_datefield'].',path
- FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
+ FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
'.$page['where'].'
- AND YEAR('.$conf['calendar_datefield'].') = '.$page['calendar_year'].'
- AND MONTH('.$conf['calendar_datefield'].') = '.$calendar_month.'
- AND id = image_id
+ AND '.$conf['calendar_datefield'].'
+ BETWEEN \''.$page['calendar_year'].'-'.$calendar_month.'-1\'
+ AND \''.$page['calendar_year'].'-'.$calendar_month.'-31\'
ORDER BY RAND()
LIMIT 0,1
;';
@@ -319,10 +320,9 @@ elseif (!isset($page['calendar_day']))
{
$query = '
SELECT file,tn_ext,'.$conf['calendar_datefield'].',path
- FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
+ FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
'.$page['where'].'
AND '.$conf['calendar_datefield'].' = \''.$calendar_day.'\'
- AND id = image_id
ORDER BY RAND()
LIMIT 0,1
;';
diff --git a/install/dbscheme.txt b/install/dbscheme.txt
index 070f7f0bc..654ccbc28 100644
--- a/install/dbscheme.txt
+++ b/install/dbscheme.txt
@@ -140,5 +140,6 @@ index:images_i2 table:images column:date_available
index:images_i1 table:images column:storage_category_id
index:images_i3 table:images column:average_rate
index:images_i4 table:images column:hit
+index:images_i5 table:images column:date_creation
index:sites_ui1 table:sites column:galleries_url
index:users_ui1 table:users column:username
diff --git a/install/phpwebgallery_structure.sql b/install/phpwebgallery_structure.sql
index d9dbf2161..d24d7d023 100644
--- a/install/phpwebgallery_structure.sql
+++ b/install/phpwebgallery_structure.sql
@@ -145,7 +145,8 @@ CREATE TABLE phpwebgallery_images (
KEY images_i2 (date_available),
KEY images_i1 (storage_category_id),
KEY images_i3 (average_rate),
- KEY images_i4 (hit)
+ KEY images_i4 (hit),
+ KEY images_i5 (date_creation)
) TYPE=MyISAM;
--