add channel_lastpost timestamp to help optimise some outrageously expensive queries.

6 files changed, 27 insertions, 3 deletions

diff --git a/boot.php b/boot.php
index 5fa15c9e6..aac8eff12 100755
--- a/boot.php
+++ b/boot.php
@@ -49,7 +49,7 @@ define ( 'RED_PLATFORM',            'redmatrix' );
 define ( 'RED_VERSION',             trim(file_get_contents('version.inc')) . 'R');
 define ( 'ZOT_REVISION',            1     );
 
-define ( 'DB_UPDATE_VERSION',       1139  );
+define ( 'DB_UPDATE_VERSION',       1140  );
 
 /**
  * Constant with a HTML line break.
diff --git a/doc/to_do_code.bb b/doc/to_do_code.bb
index 4b7b98ee1..713885f92 100644
--- a/doc/to_do_code.bb
+++ b/doc/to_do_code.bb
@@ -24,7 +24,6 @@ We need much more than this, but here are areas where developers can help. Pleas
 [li]service classes - account overview page showing resources consumed by channel. With special consideration this page can also be accessed at a meta level by the site admin to drill down on problematic accounts/channels.[/li]

 [li]Events module - fix permissions on events, and provide JS translation support for the calendar overview; integrate with calDAV[/li]

 [li]Uploads - integrate #^[url=https://github.com/blueimp/jQuery-File-Upload]https://github.com/blueimp/jQuery-File-Upload[/url][/li]

-[li]We could probably improve even more on the infamous sys_channel query by carrying a column in the channel DB for the timestamp of the last post; so we don't have to randomly select channels from which to find public content and quite possibly randomly select a bunch that are inactive. This would also be a huge win for the statistics queries which could look at the same exact field and not have to count hundreds of thousands of things.[/li]

 [li]Import/export - include events, things, etc.[/li]

 [li]Import channel from Diaspora/Friendica[/li]

 [li]MediaGoblin photo "crosspost" connector[/li]

diff --git a/install/schema_mysql.sql b/install/schema_mysql.sql
index b3f992500..c2cefd07d 100644
--- a/install/schema_mysql.sql
+++ b/install/schema_mysql.sql
@@ -219,6 +219,7 @@ CREATE TABLE IF NOT EXISTS `channel` (
   `channel_notifyflags` int(10) unsigned NOT NULL DEFAULT '65535',
   `channel_pageflags` int(10) unsigned NOT NULL DEFAULT '0',
   `channel_dirdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `channel_lastpost` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `channel_deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `channel_max_anon_mail` int(10) unsigned NOT NULL DEFAULT '10',
   `channel_max_friend_req` int(10) unsigned NOT NULL DEFAULT '10',
@@ -282,6 +283,7 @@ CREATE TABLE IF NOT EXISTS `channel` (
   KEY `channel_deleted` (`channel_deleted`),
   KEY `channel_a_republish` (`channel_a_republish`),
   KEY `channel_dirdate` (`channel_dirdate`),
+  KEY `channel_lastpost` (`channel_lastpost`),
   KEY `channel_w_like` (`channel_w_like`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
diff --git a/install/schema_postgres.sql b/install/schema_postgres.sql
index bc72be445..438b29d49 100644
--- a/install/schema_postgres.sql
+++ b/install/schema_postgres.sql
@@ -166,6 +166,7 @@ CREATE TABLE "channel" (
   "channel_notifyflags" bigint  NOT NULL DEFAULT '65535',
   "channel_pageflags" bigint  NOT NULL DEFAULT '0',
   "channel_dirdate" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
+  "channel_lastpost" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
   "channel_deleted" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
   "channel_max_anon_mail" bigint  NOT NULL DEFAULT '10',
   "channel_max_friend_req" bigint  NOT NULL DEFAULT '10',
@@ -231,6 +232,7 @@ create index "channel_deleted" on channel ("channel_deleted");
 create index "channel_a_republish" on channel ("channel_a_republish");
 create index "channel_w_like" on channel ("channel_w_like");
 create index "channel_dirdate" on channel ("channel_dirdate");
+create index "channel_lastpost" on channel ("channel_lastpost");
 CREATE TABLE "chat" (
   "chat_id" serial  NOT NULL,
   "chat_room" bigint  NOT NULL DEFAULT '0',
diff --git a/install/update.php b/install/update.php
index 75805513d..36aa20373 100644
--- a/install/update.php
+++ b/install/update.php
@@ -1,6 +1,6 @@
 <?php
 
-define( 'UPDATE_VERSION' , 1139 );
+define( 'UPDATE_VERSION' , 1140 );
 
 /**
  *
@@ -1589,3 +1589,17 @@ function update_r1138() {
 		return UPDATE_SUCCESS;
 	return UPDATE_FAILED;
 }
+
+function update_r1139() {
+	if(ACTIVE_DBTYPE == DBTYPE_POSTGRES) { 
+		$r1 = q("ALTER TABLE channel ADD channel_lastpost timestamp NOT NULL DEFAULT '0001-01-01 00:00:00'");
+		$r2 = q("create index channel_lastpost on channel ( channel_lastpost ) ");
+		$r = $r1 && $r2;
+	}
+	else
+		$r = q("ALTER TABLE `channel` ADD `channel_lastpost` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER `channel_dirdate` , ADD INDEX ( `channel_lastpost` ) ");
+	if($r)
+		return UPDATE_SUCCESS;
+	return UPDATE_FAILED;
+
+}
\ No newline at end of file
diff --git a/mod/item.php b/mod/item.php
index 8aa3d8136..fa40aea03 100644
--- a/mod/item.php
+++ b/mod/item.php
@@ -860,6 +860,13 @@ function item_post(&$a) {
 					'otype'        => 'item'
 				));
 			}
+
+			if($uid && $uid == $profile_uid && (! $datarray['item_restrict'])) {
+				q("update channel set channel_lastpost = '%s' where channel_id = %d",
+					dbesc(datetime_convert()),
+					intval($uid)
+				);
+			}
 		}
 
 		// photo comments turn the corresponding item visible to the profile wall