query optimisations for notifications - use a specific index only

5 files changed, 23 insertions, 7 deletions

diff --git a/Zotlabs/Module/Hq.php b/Zotlabs/Module/Hq.php
index c305fdb3b..c46695b65 100644
--- a/Zotlabs/Module/Hq.php
+++ b/Zotlabs/Module/Hq.php
@@ -51,9 +51,11 @@ class Hq extends \Zotlabs\Web\Controller {
 		$item_normal = item_normal();
 		$item_normal_update = item_normal_update();
 
+		$use_index = db_use_index('created');
+
 		if(! $item_hash) {
-			$r = q("SELECT mid FROM item
-				WHERE uid = %d
+			$r = q("SELECT mid FROM item $use_index
+				WHERE uid = %d $item_normal
 				AND mid = parent_mid 
 				ORDER BY created DESC LIMIT 1",
 				intval(local_channel())
diff --git a/Zotlabs/Module/Ping.php b/Zotlabs/Module/Ping.php
index a3f6cdfec..f8399d871 100644
--- a/Zotlabs/Module/Ping.php
+++ b/Zotlabs/Module/Ping.php
@@ -320,7 +320,9 @@ class Ping extends \Zotlabs\Web\Controller {
 		if(argc() > 1 && (argv(1) === 'network' || argv(1) === 'home')) {
 			$result = array();
 
-			$r = q("SELECT * FROM item
+			$use_index = db_use_index('uid_item_unseen');
+
+			$r = q("SELECT * FROM item $use_index
 				WHERE item_unseen = 1 and uid = %d $item_normal
 				AND author_xchan != '%s'
 				ORDER BY created DESC limit 300",
@@ -492,8 +494,10 @@ class Ping extends \Zotlabs\Web\Controller {
 		$t3 = dba_timer();
 
 		if($vnotify & (VNOTIFY_NETWORK|VNOTIFY_CHANNEL)) {
+
+			$use_index = db_use_index('uid_item_unseen');
 			
-			$r = q("SELECT id, item_wall FROM item
+			$r = q("SELECT id, item_wall FROM item $use_index
 				WHERE item_unseen = 1 and uid = %d
 				$item_normal
 				AND author_xchan != '%s'",
diff --git a/include/dba/dba_driver.php b/include/dba/dba_driver.php
index 7e925a106..deec9adfd 100755
--- a/include/dba/dba_driver.php
+++ b/include/dba/dba_driver.php
@@ -321,6 +321,10 @@ function db_concat($fld, $sep) {
 	return \DBA::$dba->concat($fld, $sep);
 }
 
+function db_use_index($str) {
+	return \DBA::$dba->use_index($str);
+}
+
 /**
  * @brief Execute a SQL query with printf style args.
  *
diff --git a/include/dba/dba_pdo.php b/include/dba/dba_pdo.php
index 7b58561a7..a9d824a50 100755
--- a/include/dba/dba_pdo.php
+++ b/include/dba/dba_pdo.php
@@ -111,6 +111,15 @@ class dba_pdo extends dba_driver {
 		}
 	}
 
+	function use_index($str) {
+		if($this->driver_dbtype === 'pgsql') {
+			return '';
+		}
+		else {
+			return 'USE INDEX( ' . $str . ')';
+		}
+	}
+
 	function quote_interval($txt) {
 		if($this->driver_dbtype === 'pgsql') {
 			return "'$txt'";
diff --git a/view/tpl/notifications_widget.tpl b/view/tpl/notifications_widget.tpl
index 8563dc7fa..0f27b1a47 100644
--- a/view/tpl/notifications_widget.tpl
+++ b/view/tpl/notifications_widget.tpl
@@ -52,9 +52,6 @@
 				e.preventDefault();
 
 				if(! page_load) {
-					if($(this).parent().attr('id') !== 'nav-pubs-menu')
-						$(this).fadeOut();
-
 					getData(b64mid, notify_id);
 				}