From 10e5d05e1809b7109cbf48e2c924058a7953e426 Mon Sep 17 00:00:00 2001
From: garvinhicking <garvinhicking>
Date: Tue, 27 Feb 2007 11:20:47 +0000
Subject: [PATCH] Improve SQL query for fetching archive overview, from
 $year*$month queries to 1. :-)

---
 docs/NEWS                         |  4 ++++
 include/functions_entries.inc.php | 38 ++++++++++++++++---------------
 2 files changed, 24 insertions(+), 18 deletions(-)

diff --git a/docs/NEWS b/docs/NEWS
index 3f2167d..5ea66f4 100644
--- a/docs/NEWS
+++ b/docs/NEWS
@@ -3,6 +3,10 @@
 Version 1.2 ()
 ------------------------------------------------------------------------
 
+    * Improve performance of displaying the complete archive. Instead
+      of year*months SQL queries, only one query is now used.
+      (garvinhicking)
+ 
     * Improve installation on hosts where fsockopen() is disabled
       (garvinhicking)
       
diff --git a/include/functions_entries.inc.php b/include/functions_entries.inc.php
index 3747295..2a75027 100644
--- a/include/functions_entries.inc.php
+++ b/include/functions_entries.inc.php
@@ -1457,6 +1457,25 @@ function serendipity_printArchives() {
         $author_get = '';
     }
 
+    $q = "SELECT e.timestamp
+            FROM {$serendipity['dbPrefix']}entries e
+            " . (!empty($cat_sql) ? "
+       LEFT JOIN {$serendipity['dbPrefix']}entrycat ec
+              ON e.id = ec.entryid
+       LEFT JOIN {$serendipity['dbPrefix']}category c
+              ON ec.categoryid = c.categoryid" : "") . "
+           WHERE isdraft = 'false'"
+                . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND timestamp <= " . serendipity_db_time() : '')
+                . (!empty($cat_sql) ? ' AND ' . $cat_sql : '')
+                . (!empty($serendipity['GET']['viewAuthor']) ? ' AND e.authorid = ' . (int)$serendipity['GET']['viewAuthor'] : '') 
+                . (!empty($cat_sql) ? " GROUP BY e.id" : '');
+    $entries =& serendipity_db_query($q, false, 'assoc');
+
+    $group = array();
+    foreach($entries AS $entry) {
+        $group[date('Ym', $entry['timestamp'])]++;
+    }
+
     $output = array();
     for ($y = $thisYear; $y >= $lastYear; $y--) {
         $output[$y]['year'] = $y;
@@ -1485,24 +1504,7 @@ function serendipity_printArchives() {
                     break;
             }
 
-            $entries =& serendipity_db_query("SELECT count(id)
-                                               FROM {$serendipity['dbPrefix']}entries e
-                                          LEFT JOIN {$serendipity['dbPrefix']}entrycat ec
-                                                 ON e.id = ec.entryid
-                                          LEFT JOIN {$serendipity['dbPrefix']}category c
-                                                 ON ec.categoryid = c.categoryid
-                                              WHERE isdraft = 'false'
-                                                AND timestamp >= $s
-                                                AND timestamp <= $e "
-                                                    . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND timestamp <= " . serendipity_db_time() : '')
-                                                    . (!empty($cat_sql) ? ' AND ' . $cat_sql : '')
-                                                    . (!empty($serendipity['GET']['viewAuthor']) ? ' AND e.authorid = ' . (int)$serendipity['GET']['viewAuthor'] : '') . "
-                                           GROUP BY ec.entryid", false, 'assoc');
-            if (is_array($entries)) {
-                $entry_count = count($entries);
-            } else {
-                $entry_count = 0;
-            }
+            $entry_count = (int)$group[$y . (strlen($m) == 1 ? '0' : '') . $m];
 
             /* A silly hack to get the maximum amount of entries per month */
             if ($entry_count > $max) {
-- 
2.39.5