From b88c557c6bb0186749232600b7e6438033a31521 Mon Sep 17 00:00:00 2001 From: Michael Date: Sat, 16 Dec 2023 18:28:33 +0000 Subject: [PATCH] Issue 13560: new indexes to improve circle loading time --- database.sql | 6 +++- doc/database/db_post-thread-user.md | 46 ++++++++++++++++------------- static/dbstructure.config.php | 7 ++++- 3 files changed, 36 insertions(+), 23 deletions(-) diff --git a/database.sql b/database.sql index 134d1f8261..81082cb38e 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2023.09-rc (Giant Rhubarb) --- DB_UPDATE_VERSION 1541 +-- DB_UPDATE_VERSION 1542 -- ------------------------------------------ @@ -1601,8 +1601,12 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( INDEX `uid_received` (`uid`,`received`), INDEX `uid_wall_received` (`uid`,`wall`,`received`), INDEX `uid_commented` (`uid`,`commented`), + INDEX `uid_created` (`uid`,`created`), INDEX `uid_starred` (`uid`,`starred`), INDEX `uid_mention` (`uid`,`mention`), + INDEX `contact-id_commented` (`contact-id`,`commented`), + INDEX `contact-id_received` (`contact-id`,`received`), + INDEX `contact-id_created` (`contact-id`,`created`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`conversation-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, diff --git a/doc/database/db_post-thread-user.md b/doc/database/db_post-thread-user.md index b4114d13c9..a89d94c96f 100644 --- a/doc/database/db_post-thread-user.md +++ b/doc/database/db_post-thread-user.md @@ -36,27 +36,31 @@ Fields Indexes ------------ -| Name | Fields | -| ----------------- | ------------------- | -| PRIMARY | uid, uri-id | -| uri-id | uri-id | -| conversation-id | conversation-id | -| owner-id | owner-id | -| author-id | author-id | -| causer-id | causer-id | -| uid | uid | -| contact-id | contact-id | -| psid | psid | -| post-user-id | post-user-id | -| commented | commented | -| received | received | -| author-id_created | author-id, created | -| owner-id_created | owner-id, created | -| uid_received | uid, received | -| uid_wall_received | uid, wall, received | -| uid_commented | uid, commented | -| uid_starred | uid, starred | -| uid_mention | uid, mention | +| Name | Fields | +| -------------------- | --------------------- | +| PRIMARY | uid, uri-id | +| uri-id | uri-id | +| conversation-id | conversation-id | +| owner-id | owner-id | +| author-id | author-id | +| causer-id | causer-id | +| uid | uid | +| contact-id | contact-id | +| psid | psid | +| post-user-id | post-user-id | +| commented | commented | +| received | received | +| author-id_created | author-id, created | +| owner-id_created | owner-id, created | +| uid_received | uid, received | +| uid_wall_received | uid, wall, received | +| uid_commented | uid, commented | +| uid_created | uid, created | +| uid_starred | uid, starred | +| uid_mention | uid, mention | +| contact-id_commented | contact-id, commented | +| contact-id_received | contact-id, received | +| contact-id_created | contact-id, created | Foreign Keys ------------ diff --git a/static/dbstructure.config.php b/static/dbstructure.config.php index 53221d9e44..64c9bdff1f 100644 --- a/static/dbstructure.config.php +++ b/static/dbstructure.config.php @@ -56,7 +56,7 @@ use Friendica\Database\DBA; // This file is required several times during the test in DbaDefinition which justifies this condition if (!defined('DB_UPDATE_VERSION')) { - define('DB_UPDATE_VERSION', 1541); + define('DB_UPDATE_VERSION', 1542); } return [ @@ -1607,8 +1607,13 @@ return [ "uid_received" => ["uid", "received"], "uid_wall_received" => ["uid", "wall", "received"], "uid_commented" => ["uid", "commented"], + "uid_received" => ["uid", "received"], + "uid_created" => ["uid", "created"], "uid_starred" => ["uid", "starred"], "uid_mention" => ["uid", "mention"], + "contact-id_commented" => ["contact-id", "commented"], + "contact-id_received" => ["contact-id", "received"], + "contact-id_created" => ["contact-id", "created"], ] ], "post-user-notification" => [ -- 2.39.5