Changeset 612 for trunk/include

Timestamp:

Nov 21, 2004, 12:13:48 PM (20 years ago)

Author:

plg

Message:

optimization : instead of reading each $confcalendar_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)

File:

• trunk/include/category_calendar.inc.php (modified) (6 diffs)

trunk/include/category_calendar.inc.php

@@ -87 +87 @@
 SELECT DISTINCT(MONTH('.$conf['calendar_datefield'].')) AS month
      , COUNT(id) AS count
-  FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
-  '.$page['where'].'
-    AND id = image_id
-    AND YEAR('.$conf['calendar_datefield'].') = '.$page['calendar_year'].'
+  FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
+  '.$page['where'].'
+    AND '.$conf['calendar_datefield'].'
+      BETWEEN \''.$page['calendar_year'].'-1-1\'
+      AND \''.$page['calendar_year'].'-12-31\'
   GROUP BY MONTH('.$conf['calendar_datefield'].')
 ;';
@@ -158 +159 @@
   $query = '
 SELECT DISTINCT('.$conf['calendar_datefield'].') AS day, COUNT(id) AS count
-  FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
-  '.$page['where'].'
-    AND id = image_id
-    AND YEAR('.$conf['calendar_datefield'].') = '.$page['calendar_year'].'
-    AND MONTH('.$conf['calendar_datefield'].') = '.$page['calendar_month'].'
+  FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
+  '.$page['where'].'
+    AND '.$conf['calendar_datefield'].'
+      BETWEEN \''.$page['calendar_year'].'-'.$page['calendar_month'].'-1\'
+      AND \''.$page['calendar_year'].'-'.$page['calendar_month'].'-31\'
   GROUP BY day
 ;';
@@ -183 +184 @@
   $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
 ;';
@@ -221 +221 @@
     $query = '
 SELECT file,tn_ext,'.$conf['calendar_datefield'].',path
-  FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
-  '.$page['where'].'
-    AND YEAR('.$conf['calendar_datefield'].') = '.$calendar_year.'
-    AND id = image_id
+  FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
+  '.$page['where'].'
+    AND '.$conf['calendar_datefield'].'
+      BETWEEN \''.$calendar_year.'-1-1\'
+      AND \''.$calendar_year.'-12-31\'
   ORDER BY RAND()
   LIMIT 0,1
@@ -266 +267 @@
     $query = '
 SELECT file,tn_ext,'.$conf['calendar_datefield'].',path
-  FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
-  '.$page['where'].'
-    AND YEAR('.$conf['calendar_datefield'].') = '.$page['calendar_year'].'
-    AND MONTH('.$conf['calendar_datefield'].') = '.$calendar_month.'
-    AND id = image_id
+  FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
+  '.$page['where'].'
+    AND '.$conf['calendar_datefield'].'
+      BETWEEN \''.$page['calendar_year'].'-'.$calendar_month.'-1\'
+      AND \''.$page['calendar_year'].'-'.$calendar_month.'-31\'
   ORDER BY RAND()
   LIMIT 0,1
@@ -320 +321 @@
     $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