provide relief to sites that are severely impacted by the slow ITEM_UNSEEN searches. This does not incorporate any other flag optimisations as that will require a major DB update and possibly involve significant downtime. This is just to bite off a little chunk now and provide some much needed relief.

20 files changed, 52 insertions, 61 deletions

diff --git a/boot.php b/boot.php
index 81e1f6d76..f632931b6 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',       1136  );
+define ( 'DB_UPDATE_VERSION',       1137  );
 
 /**
  * Constant with a HTML line break.
diff --git a/include/ItemObject.php b/include/ItemObject.php
index c429a0131..136e08eb7 100644
--- a/include/ItemObject.php
+++ b/include/ItemObject.php
@@ -612,7 +612,7 @@ class Item extends BaseObject {
 				if((! visible_activity($child->data)) || array_key_exists('author_blocked',$child->data)) {
 					continue;
 				}
-				if($child->data['item_flags'] & ITEM_UNSEEN)
+				if(intval($child->data['item_unseen']))
 					$total ++;
 			}
 		}
diff --git a/include/api.php b/include/api.php
index d00cdcf8a..d5c2a429c 100644
--- a/include/api.php
+++ b/include/api.php
@@ -1004,10 +1004,7 @@ require_once('include/items.php');
 		// at the network timeline just mark everything seen. 
 	
 		if (api_user() == $user_info['uid']) {
-			$r = q("UPDATE `item` SET item_flags = ( item_flags & ~%d )
-				WHERE (item_flags & %d)>0 and uid = %d",
-				intval(ITEM_UNSEEN),
-				intval(ITEM_UNSEEN),
+			$r = q("UPDATE `item` SET item_unseen = 0 where item_unseen = 1 and uid = %d",
 				intval($user_info['uid'])
 			);
 		}
diff --git a/include/attach.php b/include/attach.php
index 5ed2cf6d2..3350a591e 100644
--- a/include/attach.php
+++ b/include/attach.php
@@ -998,7 +998,8 @@ function file_activity($channel_id, $object, $allow_cid, $allow_gid, $deny_cid,
 
 	$objtype = ACTIVITY_OBJ_FILE;
 
-	$item_flags = ITEM_WALL|ITEM_ORIGIN|ITEM_UNSEEN;
+	$item_flags = ITEM_WALL|ITEM_ORIGIN;
+;
 
 	$private = (($allow_cid || $allow_gid || $deny_cid || $deny_gid) ? 1 : 0);
 
@@ -1038,6 +1039,7 @@ function file_activity($channel_id, $object, $allow_cid, $allow_gid, $deny_cid,
 		$arr['mid']           = $u_mid;
 		$arr['parent_mid']    = $u_mid;
 		$arr['item_flags']    = $item_flags;
+		$arr['item_unseen']   = 1;
 		$arr['author_xchan']  = $poster['xchan_hash'];
 		$arr['owner_xchan']   = $poster['xchan_hash'];
 		$arr['title']         = '';
diff --git a/include/diaspora.php b/include/diaspora.php
index 4280d9d77..559a9d14d 100755
--- a/include/diaspora.php
+++ b/include/diaspora.php
@@ -923,8 +923,8 @@ function diaspora_post($importer,$xml,$msg) {
 
 	$datarray['app']  = $app;
 
-	$datarray['item_flags'] = ITEM_UNSEEN|ITEM_THREAD_TOP;
-
+	$datarray['item_flags'] = ITEM_THREAD_TOP;
+	$datarray['item_unseen'] = 1;
 
 	$result = item_store($datarray);
 	return;
diff --git a/include/items.php b/include/items.php
index 91fa1d70a..717a4bf71 100755
--- a/include/items.php
+++ b/include/items.php
@@ -2043,13 +2043,12 @@ function item_store($arr,$allow_exec = false) {
 
 	$arr['comment_policy'] = ((x($arr,'comment_policy')) ? notags(trim($arr['comment_policy']))  : 'contacts' );
 	
-	$arr['item_flags'] = $arr['item_flags'] | ITEM_UNSEEN;
+	$arr['item_unseen'] = ((array_key_exists('item_unseen',$arr)) ? intval($arr['item_unseen']) : 1);
 
 	if($arr['comment_policy'] == 'none')
 		$arr['item_flags'] = $arr['item_flags'] | ITEM_NOCOMMENT;
 
 
-
 	// handle time travelers
 	// Allow a bit of fudge in case somebody just has a slightly slow/fast clock
 
@@ -2332,8 +2331,8 @@ function item_store_update($arr,$allow_exec = false) {
 
 	// override the unseen flag with the original
 
-	if($arr['item_flags'] & ITEM_UNSEEN)
-		$arr['item_flags'] = $arr['item_flags'] ^ ITEM_UNSEEN;
+	if(intval($arr['item_flags']))
+		$arr['item_unseen'] = 0;
 
 	if($orig[0]['item_flags'] & ITEM_VERIFIED)
 		$orig[0]['item_flags'] = $orig[0]['item_flags'] ^ ITEM_VERIFIED;
@@ -4562,7 +4561,7 @@ function items_fetch($arr,$channel = null,$observer_hash = null,$client_mode = C
     	}
 	}
 
-    $simple_update = (($client_mode & CLIENT_MODE_UPDATE) ? " and ( item.item_flags & " . intval(ITEM_UNSEEN) . " )>0 " : '');
+    $simple_update = (($client_mode & CLIENT_MODE_UPDATE) ? " and ( item.item_unseen = 1 ) " : '');
     if($client_mode & CLIENT_MODE_LOAD)
         $simple_update = '';
 
diff --git a/include/notify.php b/include/notify.php
index aa96fa279..eef838664 100644
--- a/include/notify.php
+++ b/include/notify.php
@@ -32,7 +32,7 @@ function format_notification($item) {
 		'url' => $item['author']['xchan_url'],
 		'photo' => $item['author']['xchan_photo_s'],
 		'when' => relative_date($item['created']), 
-		'class' => (($item['item_flags'] & ITEM_UNSEEN) ? 'notify-unseen' : 'notify-seen'), 
+		'class' => (intval($item['item_unseen']) ? 'notify-unseen' : 'notify-seen'), 
 		'message' => strip_tags(bbcode($itemem_text))
 	);
 
diff --git a/install/schema_mysql.sql b/install/schema_mysql.sql
index 8addc0af9..247b33814 100644
--- a/install/schema_mysql.sql
+++ b/install/schema_mysql.sql
@@ -688,6 +688,7 @@ CREATE TABLE IF NOT EXISTS `item` (
   `item_restrict` int(11) NOT NULL DEFAULT '0',
   `item_flags` int(11) NOT NULL DEFAULT '0',
   `item_private` tinyint(4) NOT NULL DEFAULT '0',
+  `item_unseen` smallint(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   KEY `uid` (`uid`),
   KEY `parent` (`parent`),
@@ -717,6 +718,7 @@ CREATE TABLE IF NOT EXISTS `item` (
   KEY `public_policy` (`public_policy`),
   KEY `comments_closed` (`comments_closed`),
   KEY `changed` (`changed`),
+  KEY `item_unseen` (`item_unseen`),
   FULLTEXT KEY `title` (`title`),
   FULLTEXT KEY `body` (`body`),
   FULLTEXT KEY `allow_cid` (`allow_cid`),
diff --git a/install/schema_postgres.sql b/install/schema_postgres.sql
index b68a7cb97..d852f38e9 100644
--- a/install/schema_postgres.sql
+++ b/install/schema_postgres.sql
@@ -528,6 +528,7 @@ CREATE TABLE "item" (
   "item_restrict" bigint NOT NULL DEFAULT '0',
   "item_flags" bigint NOT NULL DEFAULT '0',
   "item_private" numeric(4) NOT NULL DEFAULT '0',
+  "item_unseen" smallint(1) NOT NULL DEFAULT '0',
   "item_search_vector" tsvector,
   PRIMARY KEY ("id")
 );
@@ -559,6 +560,7 @@ create index "item_uid_mid" on item ("mid","uid");
 create index "item_public_policy" on item ("public_policy");
 create index "item_comment_policy" on item ("comment_policy");
 create index "item_layout_mid" on item ("layout_mid");
+create index "item_unseen" on item ("item_unseen");
 
 -- fulltext indexes
 create index "item_search_idx" on  item USING gist("item_search_vector");
diff --git a/install/update.php b/install/update.php
index 2c9e54ff9..d2f1ead3c 100644
--- a/install/update.php
+++ b/install/update.php
@@ -1,6 +1,6 @@
 <?php
 
-define( 'UPDATE_VERSION' , 1136 );
+define( 'UPDATE_VERSION' , 1137 );
 
 /**
  *
@@ -1558,3 +1558,13 @@ function update_r1135() {
 		return UPDATE_SUCCESS;
 	return UPDATE_FAILED;
 }
+
+function update_r1136() {
+	$r1 = q("alter table item add item_unseen smallint(1) not null default '0' ");
+	$r2 = q("create index item_unseen on item ( item_unseen ) ");
+	$r3 = q("update item set item_unseen = 1 where ( item_flags & 2 ) > 0 ");
+
+	if($r1 && $r2 && $r3)
+		return UPDATE_SUCCESS;
+	return UPDATE_FAILED;
+}
diff --git a/mod/channel.php b/mod/channel.php
index 1a471718d..788bacf70 100644
--- a/mod/channel.php
+++ b/mod/channel.php
@@ -147,23 +147,21 @@ function channel_content(&$a, $update = 0, $load = false) {
 	if(($update) && (! $load)) {
 		if ($mid) {
 			$r = q("SELECT parent AS item_id from item where mid = '%s' and uid = %d AND item_restrict = 0
-				AND (item_flags &  %d) > 0 AND (item_flags & %d) > 0 $sql_extra limit 1",
+				AND (item_flags &  %d) > 0 AND item_unseen = 1 $sql_extra limit 1",
 				dbesc($mid),
 				intval($a->profile['profile_uid']),
-				intval(ITEM_WALL),
-				intval(ITEM_UNSEEN)
+				intval(ITEM_WALL)
 			);
 		} else {
 			$r = q("SELECT distinct parent AS `item_id`, created from item
 				left join abook on item.author_xchan = abook.abook_xchan
 				WHERE uid = %d AND item_restrict = 0
-				AND (item_flags &  %d) > 0 AND ( item_flags & %d ) > 0
+				AND (item_flags &  %d) > 0 AND item_unseen = 1
 				AND ((abook.abook_flags & %d) = 0 or abook.abook_flags is null)
 				$sql_extra
 				ORDER BY created DESC",
 				intval($a->profile['profile_uid']),
 				intval(ITEM_WALL),
-				intval(ITEM_UNSEEN),
 				intval(ABOOK_FLAG_BLOCKED)
 			);
 		}
@@ -317,10 +315,8 @@ function channel_content(&$a, $update = 0, $load = false) {
 	}
 
 	if($is_owner && $update_unseen) {
-		$r = q("UPDATE item SET item_flags = (item_flags & ~%d)
-			WHERE (item_flags & %d) > 0 AND (item_flags & %d) > 0 AND uid = %d $update_unseen",
-			intval(ITEM_UNSEEN),
-			intval(ITEM_UNSEEN),
+		$r = q("UPDATE item SET item_unseen = 0 WHERE item_unseen = 1
+			AND (item_flags & %d) > 0 AND uid = %d $update_unseen",
 			intval(ITEM_WALL),
 			intval(local_channel())
 		);
diff --git a/mod/display.php b/mod/display.php
index 467c476da..c2e5c2426 100644
--- a/mod/display.php
+++ b/mod/display.php
@@ -231,10 +231,7 @@ function display_content(&$a, $update = 0, $load = false) {
 	}
 
 	if($updateable) {
-		$x = q("UPDATE item SET item_flags = ( item_flags & ~%d )
-			WHERE (item_flags & %d)>0 AND uid = %d and parent = %d ",
-			intval(ITEM_UNSEEN),
-			intval(ITEM_UNSEEN),
+		$x = q("UPDATE item SET item_unseen = 0 WHERE item_unseen = 1 AND uid = %d and parent = %d ",
 			intval(local_channel()),
 			intval($r[0]['parent'])
 		);
diff --git a/mod/home.php b/mod/home.php
index 854e2df26..db4ae9c42 100644
--- a/mod/home.php
+++ b/mod/home.php
@@ -170,7 +170,7 @@ function home_content(&$a, $update = 0, $load = false) {
 
 			$page_mode = 'list';
 
-			$simple_update = (($update) ? " and ( item.item_flags & " . intval(ITEM_UNSEEN) . " ) > 0 " : '');
+			$simple_update = (($update) ? " and item.item_unseen = 1 " : '');
 
 			if($update && $_SESSION['loadtime'])
 				$simple_update .= " and item.changed > '" . datetime_convert('UTC','UTC',$_SESSION['loadtime']) . "' ";
diff --git a/mod/item.php b/mod/item.php
index ef0491895..99d28400b 100644
--- a/mod/item.php
+++ b/mod/item.php
@@ -643,8 +643,7 @@ function item_post(&$a) {
 		}
 	}
 
-	if(local_channel() != $profile_uid)
-		$item_flags |= ITEM_UNSEEN;
+	$item_unseen =  ((local_channel() != $profile_uid) ? 1 : 0);
 	
 	if($post_type === 'wall' || $post_type === 'wall-comment')
 		$item_flags = $item_flags | ITEM_WALL;
@@ -694,7 +693,7 @@ function item_post(&$a) {
 	
 	$datarray['aid']            = $channel['channel_account_id'];
 	$datarray['uid']            = $profile_uid;
-
+	
 	$datarray['owner_xchan']    = (($owner_hash) ? $owner_hash : $owner_xchan['xchan_hash']);
 	$datarray['author_xchan']   = $observer['xchan_hash'];
 	$datarray['created']        = $created;
@@ -729,6 +728,7 @@ function item_post(&$a) {
 	$datarray['term']           = $post_tags;
 	$datarray['plink']          = $plink;
 	$datarray['route']          = $route;
+	$datarray['item_unseen']    = $item_unseen;
 
 	// preview mode - prepare the body for display and send it via json
 
diff --git a/mod/manage.php b/mod/manage.php
index 4a7b25068..cb46a1b76 100644
--- a/mod/manage.php
+++ b/mod/manage.php
@@ -57,9 +57,7 @@ function manage_content(&$a) {
 
 
 				$c = q("SELECT id, item_restrict, item_flags FROM item
-					WHERE (item_restrict = %d) and ( item_flags & %d )>0 and uid = %d",
-					intval(ITEM_VISIBLE),
-					intval(ITEM_UNSEEN),
+					WHERE item_restrict = 0 and item_unseen = 1 and uid = %d",
 					intval($channels[$x]['channel_id'])
 				);
 
diff --git a/mod/mood.php b/mod/mood.php
index 4fe1ceb59..7663b2632 100755
--- a/mod/mood.php
+++ b/mod/mood.php
@@ -61,11 +61,10 @@ function mood_init(&$a) {
 	$mid = item_message_id();
 
 	$action = sprintf( t('%1$s is %2$s','mood'), '[zrl=' . $poster['xchan_url'] . ']' . $poster['xchan_name'] . '[/zrl]' , $verbs[$verb]); 
-	$item_flags = ITEM_WALL|ITEM_ORIGIN|ITEM_UNSEEN;
+	$item_flags = ITEM_WALL|ITEM_ORIGIN;
 	if(! $parent_mid)
 		$item_flags |= ITEM_THREAD_TOP;
 
-
 	$arr = array();
 
 	$arr['aid']           = get_account_id();
diff --git a/mod/network.php b/mod/network.php
index 9a4707b4d..236d4c5e0 100644
--- a/mod/network.php
+++ b/mod/network.php
@@ -346,7 +346,7 @@ function network_content(&$a, $update = 0, $load = false) {
 	else
 		$page_mode = 'client';
 
-	$simple_update = (($update) ? " and ( item.item_flags & " . intval(ITEM_UNSEEN) . " ) > 0 " : '');
+	$simple_update = (($update) ? " and item.unseen = 1 " : '');
 
 	// This fixes a very subtle bug so I'd better explain it. You wake up in the morning or return after a day
 	// or three and look at your matrix page - after opening up your browser. The first page loads just as it
@@ -465,10 +465,7 @@ function network_content(&$a, $update = 0, $load = false) {
 	}
 
 	if(($update_unseen) && (! $firehose))
-		$r = q("UPDATE item SET item_flags = ( item_flags & ~%d)
-			WHERE (item_flags & %d) > 0 AND uid = %d $update_unseen ",
-			intval(ITEM_UNSEEN),
-			intval(ITEM_UNSEEN),
+		$r = q("UPDATE item SET item_unseen = 0 where item_unseen = 1 AND uid = %d $update_unseen ",
 			intval(local_channel())
 		);
 
diff --git a/mod/photos.php b/mod/photos.php
index dbe165f60..33854dd76 100644
--- a/mod/photos.php
+++ b/mod/photos.php
@@ -877,11 +877,9 @@ function photos_content(&$a) {
 			}
 
 			if((local_channel()) && (local_channel() == $link_item['uid'])) {
-				q("UPDATE `item` SET item_flags = (item_flags & ~%d) WHERE parent = %d and uid = %d and (item_flags & %d)>0",
-					intval(ITEM_UNSEEN),
+				q("UPDATE `item` SET item_unseen = 0 WHERE item_unseen = 1 AND parent = %d AND uid = %d ",
 					intval($link_item['parent']),
-					intval(local_channel()),
-					intval(ITEM_UNSEEN)
+					intval(local_channel())
 				);
 			}
 		}
diff --git a/mod/ping.php b/mod/ping.php
index 6a89ad5cf..f49789be5 100644
--- a/mod/ping.php
+++ b/mod/ping.php
@@ -151,16 +151,12 @@ function ping_init(&$a) {
 	if(x($_REQUEST, 'markRead') && local_channel()) {
 		switch($_REQUEST['markRead']) {
 			case 'network':
-				$r = q("update item set item_flags = ( item_flags & ~%d ) where (item_flags & %d) > 0 and uid = %d", 
-					intval(ITEM_UNSEEN),
-					intval(ITEM_UNSEEN),
+				$r = q("update item set item_unseen = 0 where item_unseen = 1 and uid = %d", 
 					intval(local_channel())
 				);
 				break;
 			case 'home':
-				$r = q("update item set item_flags = ( item_flags & ~%d ) where (item_flags & %d) > 0 and (item_flags & %d) > 0  and uid = %d", 
-					intval(ITEM_UNSEEN),
-					intval(ITEM_UNSEEN),
+				$r = q("update item set item_unseen = 0 where item_unseen = 1 and (item_flags & %d) > 0  and uid = %d", 
 					intval(ITEM_WALL),
 					intval(local_channel())
 				);
@@ -190,8 +186,7 @@ function ping_init(&$a) {
 	}
 
 	if(x($_REQUEST, 'markItemRead') && local_channel()) {
-		$r = q("update item set item_flags = ( item_flags & ~%d ) where parent = %d and uid = %d", 
-			intval(ITEM_UNSEEN),
+		$r = q("update item set item_unseen = 0 where parent = %d and uid = %d", 
 			intval($_REQUEST['markItemRead']),
 			intval(local_channel())
 		);
@@ -278,10 +273,8 @@ function ping_init(&$a) {
 		$result = array();
 
 		$r = q("SELECT * FROM item
-			WHERE item_restrict = %d and ( item_flags & %d ) > 0 and uid = %d
+			WHERE item_restrict = 0 and item_unseen = 1 and uid = %d
 			and author_xchan != '%s' ORDER BY created DESC",
-			intval(ITEM_VISIBLE),
-			intval(ITEM_UNSEEN),
 			intval(local_channel()),
 			dbesc($ob_hash)
 		);
@@ -386,10 +379,8 @@ function ping_init(&$a) {
 
 	if($vnotify & (VNOTIFY_NETWORK|VNOTIFY_CHANNEL)) {
 		$r = q("SELECT id, item_restrict, item_flags FROM item
-			WHERE (item_restrict = %d) and ( item_flags & %d ) > 0 and uid = %d
+			WHERE item_restrict = 0 and item_unseen = 1 and uid = %d
 			and author_xchan != '%s'",
-			intval(ITEM_VISIBLE),
-			intval(ITEM_UNSEEN),
 			intval(local_channel()),
 			dbesc($ob_hash)
 		);
diff --git a/view/js/main.js b/view/js/main.js
index fac5e1143..17ea42db7 100644
--- a/view/js/main.js
+++ b/view/js/main.js
@@ -1155,6 +1155,8 @@ $(window).scroll(function () {
 		}
 
 		if($(window).scrollTop() + $(window).height() > $(document).height() - 100) {
+//		if($(window).scrollTop() > ($(document).height() - $(window).height() * 1.5 )) {
+
 			if((pageHasMoreContent) && (! loadingPage)) {
 				$('#more').hide();
 				$('#no-more').hide();
@@ -1175,6 +1177,7 @@ $(window).scroll(function () {
 		}
 
 		if($(window).scrollTop() + $(window).height() > $(document).height() - 100) {
+//		if($(window).scrollTop() > ($(document).height() - $(window).height() * 1.5 )) {
 			if((pageHasMoreContent) && (! loadingPage) && (! justifiedGalleryActive)) {
 				$('#more').hide();
 				$('#no-more').hide();