From 050c1e9ec2ca2b947a557a472e862604c6a7659e Mon Sep 17 00:00:00 2001 From: garvinhicking Date: Wed, 15 Nov 2006 10:18:43 +0000 Subject: [PATCH] Awesome MySQL tuning for our central query by Matthew Groeninger --- docs/NEWS | 6 ++++++ include/functions.inc.php | 17 +++++++++++++++++ include/functions_entries.inc.php | 16 ++++++++-------- include/plugin_internal.inc.php | 4 ++-- index.php | 2 +- 5 files changed, 34 insertions(+), 11 deletions(-) diff --git a/docs/NEWS b/docs/NEWS index 2d5e15c..a5dc57e 100644 --- a/docs/NEWS +++ b/docs/NEWS @@ -3,6 +3,12 @@ Version 1.1 () ------------------------------------------------------------------------ + * Huge SQL improvement by "caching" the current timestamp for + 5 minutes, so that an SQL string for the central logic will + stay the same for a 5 minute window, thus relying on the DB-Server + to cache query results more efficiently. Major thanks to + Matthew Groeninger. + * Enhanced nl2br plugin so that it will NOT put breaks into pre- defined tags like 
,  etc. Not enabled by default.
       Thanks to Brendon K from the forums!
diff --git a/include/functions.inc.php b/include/functions.inc.php
index 535eede..09058b6 100644
--- a/include/functions.inc.php
+++ b/include/functions.inc.php
@@ -1138,5 +1138,22 @@ function &serendipity_pickKey(&$array, $key, $default) {
     return $default;
 }
 
+/* Retrieves the current timestamp but only deals with minutes to optimize Database caching
+ * @access public
+ * @return timestamp
+ * @author Matthew Groeninger
+ */
+function serendipity_db_time() {
+    static $ts    = null;
+    static $cache = 300; // Seconds to cache
+    
+    if ($ts === null) {
+        $now = time();
+        $ts = $now - ($now % $cache) + $cache;
+    }
+
+    return $ts;
+}
+
 define("serendipity_FUNCTIONS_LOADED", true);
 /* vim: set sts=4 ts=4 expandtab : */
diff --git a/include/functions_entries.inc.php b/include/functions_entries.inc.php
index 4632409..2ec4132 100644
--- a/include/functions_entries.inc.php
+++ b/include/functions_entries.inc.php
@@ -306,9 +306,9 @@ function &serendipity_fetchEntries($range = null, $full = true, $limit = '', $fe
 
     if (!isset($serendipity['GET']['adminModule']) && !serendipity_db_bool($serendipity['showFutureEntries'])) {
         if (!empty($cond['and'])) {
-            $cond['and'] .= " AND e.timestamp <= " . time();
+            $cond['and'] .= " AND e.timestamp <= " . serendipity_db_time();
         } else {
-            $cond['and'] = "WHERE e.timestamp <= " . time();
+            $cond['and'] = "WHERE e.timestamp <= " . serendipity_db_time();
         }
     }
 
@@ -467,7 +467,7 @@ function &serendipity_fetchEntry($key, $val, $full = true, $fetchDrafts = 'false
     $cond['and'] = " "; // intentional dummy string to attach dummy AND parts to the WHERE clauses
 
     if ($fetchDrafts == 'false') {
-        $cond['and'] = " AND e.isdraft = 'false' " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp <= " . time() : '');
+        $cond['and'] = " AND e.isdraft = 'false' " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp <= " . serendipity_db_time() : '');
     }
 
     if (isset($serendipity['GET']['adminModule']) && $serendipity['GET']['adminModule'] == 'entries' && !serendipity_checkPermission('adminEntriesMaintainOthers')) {
@@ -715,7 +715,7 @@ function &serendipity_searchEntries($term, $limit = '') {
         }
     }
 
-    $cond['and'] = " AND isdraft = 'false' " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND timestamp <= " . time() : '');
+    $cond['and'] = " AND isdraft = 'false' " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND timestamp <= " . serendipity_db_time() : '');
     serendipity_plugin_api::hook_event('frontend_fetchentries', $cond, array('source' => 'search', 'term' => $term));
 
     serendipity_ACL_SQL($cond, 'limited');
@@ -1471,7 +1471,7 @@ function serendipity_printArchives() {
                                               WHERE isdraft = 'false'
                                                 AND timestamp >= $s
                                                 AND timestamp <= $e "
-                                                    . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND timestamp <= " . time() : '')
+                                                    . (!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');
@@ -1516,19 +1516,19 @@ function serendipity_getTotalCount($what) {
             $res = serendipity_db_query("SELECT SUM(e.comments) AS sum
                                            FROM {$serendipity['dbPrefix']}entries AS e
                                           WHERE e.isdraft = 'false'
-                                                " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp  <= " . time() : ''), true, 'assoc');
+                                                " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp  <= " . serendipity_db_time() : ''), true, 'assoc');
             return $res['sum'];
         case 'trackbacks':
             $res = serendipity_db_query("SELECT SUM(e.trackbacks) AS sum
                                            FROM {$serendipity['dbPrefix']}entries AS e
                                           WHERE e.isdraft = 'false'
-                                                " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp  <= " . time() : ''), true, 'assoc');
+                                                " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp  <= " . serendipity_db_time() : ''), true, 'assoc');
             return $res['sum'];
         case 'entries':
             $res = serendipity_db_query("SELECT COUNT(e.id) AS sum
                                            FROM {$serendipity['dbPrefix']}entries AS e
                                           WHERE e.isdraft = 'false'
-                                                " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp  <= " . time() : ''), true, 'assoc');
+                                                " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp  <= " . serendipity_db_time() : ''), true, 'assoc');
             return $res['sum'];
 
     }
diff --git a/include/plugin_internal.inc.php b/include/plugin_internal.inc.php
index 8a5cc05..17cc3f4 100644
--- a/include/plugin_internal.inc.php
+++ b/include/plugin_internal.inc.php
@@ -181,7 +181,7 @@ class serendipity_calendar_plugin extends serendipity_plugin {
         $cond = array();
         $cond['and']     = "WHERE e.timestamp  >= " . serendipity_serverOffsetHour($firstts, true) . "
                               AND e.timestamp  <= " . serendipity_serverOffsetHour($endts, true) . "
-                                  " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp  <= " . time() : '') . "
+                                  " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp  <= " . serendipity_db_time() : '') . "
                               AND e.isdraft     = 'false'";
 
         serendipity_plugin_api::hook_event('frontend_fetchentries', $cond, array('noCache' => false, 'noSticky' => false, 'source' => 'calendar'));
@@ -1440,7 +1440,7 @@ class serendipity_categories_plugin extends serendipity_plugin {
                                             WHERE ec.categoryid = c.categoryid
                                               AND ec.entryid = e.id
                                               AND e.isdraft = 'false'
-                                                  " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp  <= " . time() : '') . "
+                                                  " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND e.timestamp  <= " . serendipity_db_time() : '') . "
                                             GROUP BY c.categoryid, c.category_name
                                             ORDER BY postings DESC";
             $category_rows  = serendipity_db_query($cat_sql);
diff --git a/index.php b/index.php
index 1c49165..741efe5 100644
--- a/index.php
+++ b/index.php
@@ -270,7 +270,7 @@ if (preg_match(PAT_ARCHIVES, $uri, $matches) || isset($serendipity['GET']['range
     $_GET['serendipity']['action'] = 'read';
     $_GET['serendipity']['id']     = $id;
 
-    $title = serendipity_db_query("SELECT title FROM {$serendipity['dbPrefix']}entries WHERE id=$id AND isdraft = 'false' " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND timestamp <= " . time() : ''), true);
+    $title = serendipity_db_query("SELECT title FROM {$serendipity['dbPrefix']}entries WHERE id=$id AND isdraft = 'false' " . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND timestamp <= " . serendipity_db_time() : ''), true);
     if (is_array($title)) {
         $serendipity['head_title']    = htmlspecialchars($title[0]);
         $serendipity['head_subtitle'] = htmlspecialchars($serendipity['blogTitle']);
-- 
2.39.5