Speed improvement

[friendica.git] / database.sql

diff --git a/database.sql b/database.sql
index e1a8f7fe7c80e9b0b2b31fc0fbf03d580fd9de88..3622e27930c845f70aa1ace683710fff2d409003 100644 (file)
--- a/database.sql
+++ b/database.sql
@@ -1,6 +1,6 @@
 -- ------------------------------------------
 -- Friendica 2020.09-dev (Red Hot Poker)
--- DB_UPDATE_VERSION 1361
+-- DB_UPDATE_VERSION 1363
 -- ------------------------------------------
 
 
@@ -922,7 +922,8 @@ CREATE TABLE IF NOT EXISTS `photo` (
         INDEX `uid_profile` (`uid`,`profile`),
         INDEX `uid_album_scale_created` (`uid`,`album`(32),`scale`,`created`),
         INDEX `uid_album_resource-id_created` (`uid`,`album`(32),`resource-id`,`created`),
-        INDEX `resource-id` (`resource-id`)
+        INDEX `resource-id` (`resource-id`),
+       FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='photo storage';
 
 --
@@ -1411,11 +1412,12 @@ CREATE VIEW `network-item-view` AS SELECT
        `item`.`contact-id` AS `contact-id`
        FROM `item`
                        INNER JOIN `thread` ON `thread`.`iid` = `item`.`parent`
-                       STRAIGHT_JOIN `contact` ON `contact`.`id` = `thread`.`contact-id` AND (NOT `contact`.`blocked` OR `contact`.`pending`)
+                       STRAIGHT_JOIN `contact` ON `contact`.`id` = `thread`.`contact-id`
                        LEFT JOIN `user-item` ON `user-item`.`iid` = `item`.`id` AND `user-item`.`uid` = `thread`.`uid`
                        LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `thread`.`uid` AND `author`.`cid` = `thread`.`author-id`
                        LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `thread`.`uid` AND `owner`.`cid` = `thread`.`owner-id`
                        WHERE `thread`.`visible` AND NOT `thread`.`deleted` AND NOT `thread`.`moderated`
+                       AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`)
                        AND (`user-item`.`hidden` IS NULL OR NOT `user-item`.`hidden`)
                        AND (`author`.`blocked` IS NULL OR NOT `author`.`blocked`)
                        AND (`owner`.`blocked` IS NULL OR NOT `owner`.`blocked`);
@@ -1438,12 +1440,13 @@ CREATE VIEW `network-thread-view` AS SELECT
        `thread`.`network` AS `network`,
        `thread`.`contact-id` AS `contact-id`
        FROM `thread`
-                       STRAIGHT_JOIN `contact` ON `contact`.`id` = `thread`.`contact-id` AND (NOT `contact`.`blocked` OR `contact`.`pending`)
+                       STRAIGHT_JOIN `contact` ON `contact`.`id` = `thread`.`contact-id`
                        STRAIGHT_JOIN `item` ON `item`.`id` = `thread`.`iid`
                        LEFT JOIN `user-item` ON `user-item`.`iid` = `item`.`id` AND `user-item`.`uid` = `thread`.`uid`
                        LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `thread`.`uid` AND `author`.`cid` = `thread`.`author-id`
                        LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `thread`.`uid` AND `owner`.`cid` = `thread`.`owner-id`
                        WHERE `thread`.`visible` AND NOT `thread`.`deleted` AND NOT `thread`.`moderated`
+                       AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`)
                        AND (`user-item`.`hidden` IS NULL OR NOT `user-item`.`hidden`)
                        AND (`author`.`blocked` IS NULL OR NOT `author`.`blocked`)
                        AND (`owner`.`blocked` IS NULL OR NOT `owner`.`blocked`);