From a86b260f736cd7298d5d160c658bb9405ad3f69f Mon Sep 17 00:00:00 2001 From: Mario Vavti Date: Sun, 28 Jan 2018 12:09:47 +0100 Subject: query optimisations for notifications - use a specific index only --- Zotlabs/Module/Ping.php | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) (limited to 'Zotlabs/Module/Ping.php') 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'", -- cgit v1.2.3 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