From 9d1df58759b4eff9439c12d081ba5e1264f900a3 Mon Sep 17 00:00:00 2001 From: friendica Date: Tue, 10 Mar 2015 18:55:56 -0700 Subject: sql optimisation for affinity searches. A new index was added which wasn't added retro-actively to existing DBs as an update. It isn't clear if this helps sites any more than just restricting the abook table to certain channel_id's is (and this field is already indexed). --- install/schema_mysql.sql | 3 ++- install/schema_postgres.sql | 1 + mod/network.php | 8 +++++--- 3 files changed, 8 insertions(+), 4 deletions(-) diff --git a/install/schema_mysql.sql b/install/schema_mysql.sql index 4e481f5c0..3567085ce 100644 --- a/install/schema_mysql.sql +++ b/install/schema_mysql.sql @@ -33,7 +33,8 @@ CREATE TABLE IF NOT EXISTS `abook` ( KEY `abook_profile` (`abook_profile`), KEY `abook_dob` (`abook_dob`), KEY `abook_connected` (`abook_connected`), - KEY `abook_rating` (`abook_rating`) + KEY `abook_rating` (`abook_rating`), + KEY `abook_channel_closeness` (`book_channel`,`abook_closeness`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- diff --git a/install/schema_postgres.sql b/install/schema_postgres.sql index 20285a352..bc72be445 100644 --- a/install/schema_postgres.sql +++ b/install/schema_postgres.sql @@ -29,6 +29,7 @@ CREATE TABLE "abook" ( create index "abook_dob" on abook ("abook_dob"); create index "abook_connected" on abook ("abook_connected"); create index "abook_rating" on abook ("abook_rating"); + create index "abook_channel_closeness" on abook ("abook_channel", "abook_closeness"); CREATE TABLE "account" ( "account_id" serial NOT NULL, diff --git a/mod/network.php b/mod/network.php index 161b06804..d24d665d8 100644 --- a/mod/network.php +++ b/mod/network.php @@ -342,9 +342,11 @@ function network_content(&$a, $update = 0, $load = false) { $sys = get_sys_channel(); $uids = " and item.uid = " . intval($sys['channel_id']) . " "; $a->data['firehose'] = intval($sys['channel_id']); + $abook_uids = ""; } else { $uids = " and item.uid = " . local_channel() . " "; + $abook_uids = " and abook.abook_channel = " . local_channel() . " "; } if(get_pconfig(local_channel(),'system','network_list_mode')) @@ -376,7 +378,7 @@ function network_content(&$a, $update = 0, $load = false) { $items = q("SELECT item.*, item.id AS item_id, received FROM item left join abook on item.author_xchan = abook.abook_xchan - WHERE true $uids AND item_restrict = 0 + WHERE true $uids $abook_uids AND item_restrict = 0 and ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $simple_update $sql_extra $sql_nets @@ -407,7 +409,7 @@ function network_content(&$a, $update = 0, $load = false) { $r = q("SELECT distinct item.id AS item_id, $ordering FROM item left join abook on item.author_xchan = abook.abook_xchan - WHERE true $uids AND item.item_restrict = 0 + WHERE true $uids $abook_uids AND item.item_restrict = 0 AND item.parent = item.id and ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra3 $sql_extra $sql_nets @@ -421,7 +423,7 @@ function network_content(&$a, $update = 0, $load = false) { // update $r = q("SELECT item.parent AS item_id FROM item left join abook on item.author_xchan = abook.abook_xchan - WHERE true $uids AND item.item_restrict = 0 $simple_update + WHERE true $uids $abook_uids AND item.item_restrict = 0 $simple_update and ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra3 $sql_extra $sql_nets ", intval(ABOOK_FLAG_BLOCKED) -- cgit v1.2.3