From 3cc756f3029e5f3b02008dac94a90e168d398c9a Mon Sep 17 00:00:00 2001 From: Mario Vavti Date: Sun, 4 Feb 2018 20:42:40 +0100 Subject: remove some never used indices which prevented our item queries to find the right query execution plan in mysql and adjust some queries to optimze the result --- Zotlabs/Module/Ping.php | 19 ++++++++----------- 1 file changed, 8 insertions(+), 11 deletions(-) (limited to 'Zotlabs/Module/Ping.php') diff --git a/Zotlabs/Module/Ping.php b/Zotlabs/Module/Ping.php index f8399d871..2e86804ac 100644 --- a/Zotlabs/Module/Ping.php +++ b/Zotlabs/Module/Ping.php @@ -149,13 +149,11 @@ class Ping extends \Zotlabs\Web\Controller { $pubs = q("SELECT count(id) as total from item WHERE uid = %d AND author_xchan != '%s' - AND obj_type != '%s' AND item_unseen = 1 AND created > '" . datetime_convert('UTC','UTC',$_SESSION['static_loadtime']) . "' $item_normal", intval($sys['channel_id']), - dbesc(get_observer_hash()), - dbesc(ACTIVITY_OBJ_FILE) + dbesc(get_observer_hash()) ); if($pubs) @@ -320,12 +318,13 @@ class Ping extends \Zotlabs\Web\Controller { if(argc() > 1 && (argv(1) === 'network' || argv(1) === 'home')) { $result = array(); - $use_index = db_use_index('uid_item_unseen'); - - $r = q("SELECT * FROM item $use_index - WHERE item_unseen = 1 and uid = %d $item_normal + $r = q("SELECT * FROM item + WHERE uid = %d AND author_xchan != '%s' - ORDER BY created DESC limit 300", + AND item_unseen = 1 + $item_normal + ORDER BY created DESC, id + LIMIT 300", intval(local_channel()), dbesc($ob_hash) ); @@ -495,9 +494,7 @@ class Ping extends \Zotlabs\Web\Controller { if($vnotify & (VNOTIFY_NETWORK|VNOTIFY_CHANNEL)) { - $use_index = db_use_index('uid_item_unseen'); - - $r = q("SELECT id, item_wall FROM item $use_index + $r = q("SELECT id, item_wall FROM item WHERE item_unseen = 1 and uid = %d $item_normal AND author_xchan != '%s'", -- cgit v1.2.3 From 661c20e452255754bbccadc3b29dc3c598c07ed0 Mon Sep 17 00:00:00 2001 From: Mario Vavti Date: Thu, 8 Feb 2018 09:47:49 +0100 Subject: more db and queries finetuning --- Zotlabs/Module/Ping.php | 26 +++++++++++++------------- 1 file changed, 13 insertions(+), 13 deletions(-) (limited to 'Zotlabs/Module/Ping.php') diff --git a/Zotlabs/Module/Ping.php b/Zotlabs/Module/Ping.php index 2e86804ac..96ade22c0 100644 --- a/Zotlabs/Module/Ping.php +++ b/Zotlabs/Module/Ping.php @@ -148,8 +148,8 @@ class Ping extends \Zotlabs\Web\Controller { $pubs = q("SELECT count(id) as total from item WHERE uid = %d - AND author_xchan != '%s' AND item_unseen = 1 + AND author_xchan != '%s' AND created > '" . datetime_convert('UTC','UTC',$_SESSION['static_loadtime']) . "' $item_normal", intval($sys['channel_id']), @@ -166,8 +166,8 @@ class Ping extends \Zotlabs\Web\Controller { $r = q("SELECT * FROM item WHERE uid = %d - AND author_xchan != '%s' AND item_unseen = 1 + AND author_xchan != '%s' AND created > '" . datetime_convert('UTC','UTC',$_SESSION['static_loadtime']) . "' $item_normal ORDER BY created DESC @@ -208,22 +208,22 @@ class Ping extends \Zotlabs\Web\Controller { if(x($_REQUEST, 'markRead') && local_channel()) { switch($_REQUEST['markRead']) { case 'network': - $r = q("update item set item_unseen = 0 where item_unseen = 1 and uid = %d", + $r = q("UPDATE item SET item_unseen = 0 WHERE uid = %d AND item_unseen = 1", intval(local_channel()) ); break; case 'home': - $r = q("update item set item_unseen = 0 where item_unseen = 1 and item_wall = 1 and uid = %d", + $r = q("UPDATE item SET item_unseen = 0 WHERE uid = %d AND item_unseen = 1 AND item_wall = 1", intval(local_channel()) ); break; case 'mail': - $r = q("update mail set mail_seen = 1 where mail_seen = 0 and channel_id = %d ", + $r = q("UPDATE mail SET mail_seen = 1 WHERE channel_id = %d AND mail_seen = 0", intval(local_channel()) ); break; case 'all_events': - $r = q("update event set dismissed = 1 where dismissed = 0 and uid = %d AND dtstart < '%s' AND dtstart > '%s' ", + $r = q("UPDATE event SET dismissed = 1 WHERE uid = %d AND dismissed = 0 AND dtstart < '%s' AND dtstart > '%s' ", intval(local_channel()), dbesc(datetime_convert('UTC', date_default_timezone_get(), 'now + ' . intval($evdays) . ' days')), dbesc(datetime_convert('UTC', date_default_timezone_get(), 'now - 1 days')) @@ -243,9 +243,9 @@ class Ping extends \Zotlabs\Web\Controller { } if(x($_REQUEST, 'markItemRead') && local_channel()) { - $r = q("update item set item_unseen = 0 where parent = %d and uid = %d", - intval($_REQUEST['markItemRead']), - intval(local_channel()) + $r = q("UPDATE item SET item_unseen = 0 WHERE uid = %d AND parent = %d", + intval(local_channel()), + intval($_REQUEST['markItemRead']) ); } @@ -254,7 +254,7 @@ class Ping extends \Zotlabs\Web\Controller { * dropdown menu. */ if(argc() > 1 && argv(1) === 'notify') { - $t = q("select * from notify where uid = %d and seen = 0 order by created desc", + $t = q("SELECT * FROM notify WHERE uid = %d AND seen = 0 ORDER BY CREATED DESC", intval(local_channel()) ); @@ -320,10 +320,10 @@ class Ping extends \Zotlabs\Web\Controller { $r = q("SELECT * FROM item WHERE uid = %d - AND author_xchan != '%s' AND item_unseen = 1 + AND author_xchan != '%s' $item_normal - ORDER BY created DESC, id + ORDER BY created DESC LIMIT 300", intval(local_channel()), dbesc($ob_hash) @@ -495,7 +495,7 @@ class Ping extends \Zotlabs\Web\Controller { if($vnotify & (VNOTIFY_NETWORK|VNOTIFY_CHANNEL)) { $r = q("SELECT id, item_wall FROM item - WHERE item_unseen = 1 and uid = %d + WHERE uid = %d and item_unseen = 1 $item_normal AND author_xchan != '%s'", intval(local_channel()), -- cgit v1.2.3 From d826515ba8d2d1d78abed6701df3c2c3550e87df Mon Sep 17 00:00:00 2001 From: Mario Vavti Date: Tue, 13 Feb 2018 11:01:58 +0100 Subject: since we only save the parent mid in notify we must look for thr_parent when dealing with likes --- Zotlabs/Module/Ping.php | 13 ++++++++++++- 1 file changed, 12 insertions(+), 1 deletion(-) (limited to 'Zotlabs/Module/Ping.php') diff --git a/Zotlabs/Module/Ping.php b/Zotlabs/Module/Ping.php index 96ade22c0..eab49d69e 100644 --- a/Zotlabs/Module/Ping.php +++ b/Zotlabs/Module/Ping.php @@ -268,7 +268,18 @@ class Ping extends \Zotlabs\Web\Controller { $mid = basename($tt['link']); - $b64mid = ((strpos($mid, 'b64.' === 0)) ? $mid : 'b64.' . base64url_encode($mid)); + if(in_array($tt['verb'], [ACTIVITY_LIKE, ACTIVITY_DISLIKE])) { + // we need the thread parent + $r = q("select thr_parent from item where mid = '%s' and uid = %d limit 1", + dbesc($mid), + intval(local_channel()) + ); + + $b64mid = ((strpos($r[0]['thr_parent'], 'b64.' === 0)) ? $r[0]['thr_parent'] : 'b64.' . base64url_encode($r[0]['thr_parent'])); + } + else { + $b64mid = ((strpos($mid, 'b64.' === 0)) ? $mid : 'b64.' . base64url_encode($mid)); + } $notifs[] = array( 'notify_link' => z_root() . '/notify/view/' . $tt['id'], -- cgit v1.2.3