From 1f1c2d8ca62fb2d857b7f501a2ed16faf4dc9617 Mon Sep 17 00:00:00 2001 From: Michael Date: Tue, 1 Nov 2022 06:02:44 +0000 Subject: [PATCH] (Hopefully) query performance improvements --- src/Model/Contact/Group.php | 2 +- src/Model/Contact/Relation.php | 6 +++--- src/Model/Post.php | 8 ++++---- src/Module/Api/Mastodon/Timelines/Home.php | 2 +- src/Module/Api/Mastodon/Timelines/ListTimeline.php | 2 +- src/Module/Api/Mastodon/Timelines/PublicTimeline.php | 4 ++-- src/Module/Api/Mastodon/Timelines/Tag.php | 2 +- src/Module/Conversation/Community.php | 2 +- src/Protocol/Diaspora.php | 3 ++- src/Worker/RemoveUnusedAvatars.php | 10 +++++----- src/Worker/RemoveUnusedContacts.php | 12 ++++++++---- src/Worker/RemoveUnusedTags.php | 2 +- 12 files changed, 30 insertions(+), 25 deletions(-) diff --git a/src/Model/Contact/Group.php b/src/Model/Contact/Group.php index 52ae70807f..5a26fcaa64 100644 --- a/src/Model/Contact/Group.php +++ b/src/Model/Contact/Group.php @@ -79,7 +79,7 @@ class Group { return Contact::selectToArray([], ["`uid` = ? AND NOT `self` AND NOT `deleted` AND NOT `blocked` AND NOT `pending` AND NOT `failed` AND `id` NOT IN (SELECT DISTINCT(`contact-id`) FROM `group_member` INNER JOIN `group` ON `group`.`id` = `group_member`.`gid` - WHERE `group`.`uid` = ?)", $uid, $uid]); + WHERE `group`.`uid` = ? AND `contact-id` = `contact`.`id`)", $uid, $uid]); } /** diff --git a/src/Model/Contact/Relation.php b/src/Model/Contact/Relation.php index b8fd3aa95b..ae6859cdd9 100644 --- a/src/Model/Contact/Relation.php +++ b/src/Model/Contact/Relation.php @@ -284,7 +284,7 @@ class Relation $results = DBA::select('contact', [], ["`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ?) AND NOT `cid` IN (SELECT `id` FROM `contact` WHERE `uid` = ? AND `nurl` IN - (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?)))) + (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?))) AND `id` = `cid`) AND NOT `hidden` AND `network` IN (?, ?, ?, ?)", $cid, 0, @@ -314,7 +314,7 @@ class Relation ["`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?) AND NOT `cid` IN (SELECT `id` FROM `contact` WHERE `uid` = ? AND `nurl` IN - (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?)))) + (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?))) AND `id` = `cid`) AND NOT `hidden` AND `network` IN (?, ?, ?, ?)", $cid, 0, $uid, Contact::FRIEND, Contact::SHARING, Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus], @@ -354,7 +354,7 @@ class Relation // The query returns any contact that isn't followed by that user. $results = DBA::select('contact', [], - ["NOT `nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?)) + ["NOT `nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?) AND `nurl` = `nurl`) AND NOT `hidden` AND `uid` = ? AND `network` IN (?, ?, ?, ?)", $uid, Contact::FRIEND, Contact::SHARING, 0, Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus], diff --git a/src/Model/Post.php b/src/Model/Post.php index 31bd900128..635a84efd6 100644 --- a/src/Model/Post.php +++ b/src/Model/Post.php @@ -400,10 +400,10 @@ class Post AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (?, ?))) OR `self` OR `gravity` != ? OR `contact-uid` = ?) AND NOT `" . $view . "`.`uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `uid` = ? AND `hidden`) - AND NOT `author-id` IN (SELECT `cid` FROM `user-contact` WHERE `uid` = ? AND `blocked`) - AND NOT `owner-id` IN (SELECT `cid` FROM `user-contact` WHERE `uid` = ? AND `blocked`) - AND NOT (`gravity` = ? AND `author-id` IN (SELECT `cid` FROM `user-contact` WHERE `uid` = ? AND `ignored`)) - AND NOT (`gravity` = ? AND `owner-id` IN (SELECT `cid` FROM `user-contact` WHERE `uid` = ? AND `ignored`))", + AND NOT `author-id` IN (SELECT `cid` FROM `user-contact` WHERE `uid` = ? AND `blocked` AND `cid` = `author-id`) + AND NOT `owner-id` IN (SELECT `cid` FROM `user-contact` WHERE `uid` = ? AND `blocked` AND `cid` = `owner-id`) + AND NOT (`gravity` = ? AND `author-id` IN (SELECT `cid` FROM `user-contact` WHERE `uid` = ? AND `ignored` AND `cid` = `author-id`)) + AND NOT (`gravity` = ? AND `owner-id` IN (SELECT `cid` FROM `user-contact` WHERE `uid` = ? AND `ignored` AND `cid` = `owner-id`))", 0, Contact::SHARING, Contact::FRIEND, Item::GRAVITY_PARENT, 0, $uid, $uid, $uid, Item::GRAVITY_PARENT, $uid, Item::GRAVITY_PARENT, $uid]); $select_string = implode(', ', array_map([DBA::class, 'quoteIdentifier'], $selected)); diff --git a/src/Module/Api/Mastodon/Timelines/Home.php b/src/Module/Api/Mastodon/Timelines/Home.php index 3a18baecbf..f3cbd8d5b5 100644 --- a/src/Module/Api/Mastodon/Timelines/Home.php +++ b/src/Module/Api/Mastodon/Timelines/Home.php @@ -82,7 +82,7 @@ class Home extends BaseApi } if ($request['remote']) { - $condition = DBA::mergeConditions($condition, ["NOT `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `origin`)"]); + $condition = DBA::mergeConditions($condition, ["NOT `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `origin` AND `post-user`.`uri-id` = `post-user-view`.`uri-id`)"]); } if ($request['exclude_replies']) { diff --git a/src/Module/Api/Mastodon/Timelines/ListTimeline.php b/src/Module/Api/Mastodon/Timelines/ListTimeline.php index c786e3d9dc..df6fdb39d8 100644 --- a/src/Module/Api/Mastodon/Timelines/ListTimeline.php +++ b/src/Module/Api/Mastodon/Timelines/ListTimeline.php @@ -91,7 +91,7 @@ class ListTimeline extends BaseApi } if ($request['remote']) { - $condition = DBA::mergeConditions($condition, ["NOT `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `origin`)"]); + $condition = DBA::mergeConditions($condition, ["NOT `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `origin` AND `post-user`.`uri-id` = `post-user-view`.`uri-id`)"]); } $items = Post::selectForUser($uid, ['uri-id'], $condition, $params); diff --git a/src/Module/Api/Mastodon/Timelines/PublicTimeline.php b/src/Module/Api/Mastodon/Timelines/PublicTimeline.php index cc2392caff..066c4b83fa 100644 --- a/src/Module/Api/Mastodon/Timelines/PublicTimeline.php +++ b/src/Module/Api/Mastodon/Timelines/PublicTimeline.php @@ -65,7 +65,7 @@ class PublicTimeline extends BaseApi } if ($request['remote']) { - $condition = DBA::mergeConditions($condition, ["NOT `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `origin`)"]); + $condition = DBA::mergeConditions($condition, ["NOT `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `origin` AND `post-user`.`uri-id` = `post-view`.`uri-id`)"]); } if ($request['only_media']) { @@ -92,7 +92,7 @@ class PublicTimeline extends BaseApi if (!empty($uid)) { $condition = DBA::mergeConditions($condition, - ["NOT `parent-author-id` IN (SELECT `cid` FROM `user-contact` WHERE `uid` = ? AND (`blocked` OR `ignored`))", $uid]); + ["NOT `parent-author-id` IN (SELECT `cid` FROM `user-contact` WHERE `uid` = ? AND (`blocked` OR `ignored`) AND `cid` = `parent-author-id`)", $uid]); } $items = Post::selectPostsForUser($uid, ['uri-id'], $condition, $params); diff --git a/src/Module/Api/Mastodon/Timelines/Tag.php b/src/Module/Api/Mastodon/Timelines/Tag.php index bc4464b566..2049d92075 100644 --- a/src/Module/Api/Mastodon/Timelines/Tag.php +++ b/src/Module/Api/Mastodon/Timelines/Tag.php @@ -77,7 +77,7 @@ class Tag extends BaseApi } if ($request['remote']) { - $condition = DBA::mergeConditions($condition, ["NOT `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `origin`)"]); + $condition = DBA::mergeConditions($condition, ["NOT `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `origin` AND `post-user`.`uri-id` = `tag-search-view`.`uri-id`)"]); } if ($request['only_media']) { diff --git a/src/Module/Conversation/Community.php b/src/Module/Conversation/Community.php index b2cbf10146..7e6730f833 100644 --- a/src/Module/Conversation/Community.php +++ b/src/Module/Conversation/Community.php @@ -335,7 +335,7 @@ class Community extends BaseModule $condition[] = $item_id; } else { if (DI::userSession()->getLocalUserId() && !empty($_REQUEST['no_sharer'])) { - $condition[0] .= " AND NOT `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `post-user`.`uid` = ?)"; + $condition[0] .= " AND NOT `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `post-user`.`uid` = ? AND `post-user`.`uri-id` = `post-thread-user-view`.`uri-id`)"; $condition[] = DI::userSession()->getLocalUserId(); } diff --git a/src/Protocol/Diaspora.php b/src/Protocol/Diaspora.php index ffffaf6199..e7b66a222d 100644 --- a/src/Protocol/Diaspora.php +++ b/src/Protocol/Diaspora.php @@ -54,7 +54,8 @@ use GuzzleHttp\Psr7\Uri; use SimpleXMLElement; /** - * This class contain functions to create and send Diaspora XML files + * This class contain functions to communicate via the Diaspora protocol + * @see https://diaspora.github.io/diaspora_federation/ */ class Diaspora { diff --git a/src/Worker/RemoveUnusedAvatars.php b/src/Worker/RemoveUnusedAvatars.php index 4591fb22bf..27a6ac676f 100644 --- a/src/Worker/RemoveUnusedAvatars.php +++ b/src/Worker/RemoveUnusedAvatars.php @@ -37,11 +37,11 @@ class RemoveUnusedAvatars $sql = "FROM `contact` INNER JOIN `photo` ON `contact`.`id` = `contact-id` WHERE `contact`.`uid` = ? AND NOT `self` AND (`photo` != ? OR `thumb` != ? OR `micro` != ?) AND NOT `nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` != ?) - AND NOT `contact`.`id` IN (SELECT `author-id` FROM `post-user`) - AND NOT `contact`.`id` IN (SELECT `owner-id` FROM `post-user`) - AND NOT `contact`.`id` IN (SELECT `causer-id` FROM `post-user` WHERE `causer-id` IS NOT NULL) - AND NOT `contact`.`id` IN (SELECT `cid` FROM `post-tag`) - AND NOT `contact`.`id` IN (SELECT `contact-id` FROM `post-user`);"; + AND NOT `contact`.`id` IN (SELECT `author-id` FROM `post-user` WHERE `author-id` = `contact`.`id`) + AND NOT `contact`.`id` IN (SELECT `owner-id` FROM `post-user` WHERE `owner-id` = `contact`.`id`) + AND NOT `contact`.`id` IN (SELECT `causer-id` FROM `post-user` WHERE `causer-id` IS NOT NULL AND `causer-id` = `contact`.`id`) + AND NOT `contact`.`id` IN (SELECT `cid` FROM `post-tag` WHERE `cid` = `contact`.`id`) + AND NOT `contact`.`id` IN (SELECT `contact-id` FROM `post-user` WHERE `contact-id` = `contact`.`id`);"; $ret = DBA::fetchFirst("SELECT COUNT(*) AS `total` " . $sql, 0, '', '', '', 0); $total = $ret['total'] ?? 0; diff --git a/src/Worker/RemoveUnusedContacts.php b/src/Worker/RemoveUnusedContacts.php index 038376c22c..1cbb3775cd 100644 --- a/src/Worker/RemoveUnusedContacts.php +++ b/src/Worker/RemoveUnusedContacts.php @@ -39,10 +39,14 @@ class RemoveUnusedContacts { $condition = ["`id` != ? AND `uid` = ? AND NOT `self` AND NOT `nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` != ?) AND (NOT `network` IN (?, ?, ?, ?, ?, ?) OR (`archive` AND `success_update` < ?)) - AND NOT `id` IN (SELECT `author-id` FROM `post-user`) AND NOT `id` IN (SELECT `owner-id` FROM `post-user`) - AND NOT `id` IN (SELECT `causer-id` FROM `post-user` WHERE `causer-id` IS NOT NULL) AND NOT `id` IN (SELECT `cid` FROM `post-tag`) - AND NOT `id` IN (SELECT `contact-id` FROM `post-user`) AND NOT `id` IN (SELECT `cid` FROM `user-contact`) - AND NOT `id` IN (SELECT `cid` FROM `event`) AND NOT `id` IN (SELECT `contact-id` FROM `group_member`) + AND NOT `id` IN (SELECT `author-id` FROM `post-user` WHERE `author-id` = `contact`.`id`) + AND NOT `id` IN (SELECT `owner-id` FROM `post-user` WHERE `owner-id` = `contact`.`id`) + AND NOT `id` IN (SELECT `causer-id` FROM `post-user` WHERE `causer-id` IS NOT NULL AND `causer-id` = `contact`.`id`) + AND NOT `id` IN (SELECT `cid` FROM `post-tag` WHERE `cid` = `contact`.`id`) + AND NOT `id` IN (SELECT `contact-id` FROM `post-user` WHERE `contact-id` = `contact`.`id`) + AND NOT `id` IN (SELECT `cid` FROM `user-contact` WHERE `cid` = `contact`.`id`) + AND NOT `id` IN (SELECT `cid` FROM `event` WHERE `cid` = `contact`.`id`) + AND NOT `id` IN (SELECT `contact-id` FROM `group_member` WHERE `contact-id` = `contact`.`id`) AND `created` < ?", 0, 0, 0, Protocol::DFRN, Protocol::DIASPORA, Protocol::OSTATUS, Protocol::FEED, Protocol::MAIL, Protocol::ACTIVITYPUB, DateTimeFormat::utc('now - 365 days'), DateTimeFormat::utc('now - 30 days')]; diff --git a/src/Worker/RemoveUnusedTags.php b/src/Worker/RemoveUnusedTags.php index 90ebd673fc..ecb724c381 100644 --- a/src/Worker/RemoveUnusedTags.php +++ b/src/Worker/RemoveUnusedTags.php @@ -30,6 +30,6 @@ class RemoveUnusedTags */ public static function execute() { - DBA::delete('tag', ["NOT `id` IN (SELECT `tid` FROM `post-category`) AND NOT `id` IN (SELECT `tid` FROM `post-tag`)"]); + DBA::delete('tag', ["NOT `id` IN (SELECT `tid` FROM `post-category` WHERE `tid` = `tag`.`id`) AND NOT `id` IN (SELECT `tid` FROM `post-tag` WHERE `tid` = `tag`.`id`)"]); } } -- 2.39.5