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/Channel.php | 6 +++--- Zotlabs/Module/Hq.php | 4 +--- Zotlabs/Module/Network.php | 36 +++++++++++++++++++++++------------- Zotlabs/Module/Ping.php | 19 ++++++++----------- Zotlabs/Module/Pubstream.php | 4 ++-- Zotlabs/Widget/Forums.php | 3 ++- boot.php | 4 ++-- install/update.php | 19 ++++++++++++++++++- 8 files changed, 59 insertions(+), 36 deletions(-) diff --git a/Zotlabs/Module/Channel.php b/Zotlabs/Module/Channel.php index 7c4c900a1..b7e18f954 100644 --- a/Zotlabs/Module/Channel.php +++ b/Zotlabs/Module/Channel.php @@ -239,7 +239,7 @@ class Channel extends \Zotlabs\Web\Controller { if($load || ($checkjs->disabled())) { if($mid) { - $r = q("SELECT distinct parent AS item_id from item where mid like '%s' and uid = %d $item_normal + $r = q("SELECT parent AS item_id from item where mid like '%s' and uid = %d $item_normal AND item_wall = 1 $sql_extra limit 1", dbesc($mid . '%'), intval(\App::$profile['profile_uid']) @@ -249,13 +249,13 @@ class Channel extends \Zotlabs\Web\Controller { } } else { - $r = q("SELECT distinct id AS item_id, created FROM item + $r = q("SELECT id AS item_id FROM item left join abook on item.author_xchan = abook.abook_xchan WHERE uid = %d $item_normal AND item_wall = 1 and item_thread_top = 1 AND (abook_blocked = 0 or abook.abook_flags is null) $sql_extra $sql_extra2 - ORDER BY created DESC $pager_sql ", + ORDER BY created DESC, id $pager_sql ", intval(\App::$profile['profile_uid']) ); } diff --git a/Zotlabs/Module/Hq.php b/Zotlabs/Module/Hq.php index c46695b65..baeba82e8 100644 --- a/Zotlabs/Module/Hq.php +++ b/Zotlabs/Module/Hq.php @@ -51,10 +51,8 @@ class Hq extends \Zotlabs\Web\Controller { $item_normal = item_normal(); $item_normal_update = item_normal_update(); - $use_index = db_use_index('created'); - if(! $item_hash) { - $r = q("SELECT mid FROM item $use_index + $r = q("SELECT mid FROM item WHERE uid = %d $item_normal AND mid = parent_mid ORDER BY created DESC LIMIT 1", diff --git a/Zotlabs/Module/Network.php b/Zotlabs/Module/Network.php index 551303984..70e0048fb 100644 --- a/Zotlabs/Module/Network.php +++ b/Zotlabs/Module/Network.php @@ -209,8 +209,11 @@ class Network extends \Zotlabs\Web\Controller { : ''); $sql_nets = ''; + + $distinct = ''; + $item_thread_top = ' AND item_thread_top = 1 '; - $sql_extra = " AND item.parent IN ( SELECT parent FROM item WHERE item_thread_top = 1 $sql_options ) "; + $sql_extra = $sql_options; if($group) { $contact_str = ''; @@ -226,7 +229,8 @@ class Network extends \Zotlabs\Web\Controller { $contact_str = ' 0 '; info( t('Privacy group is empty')); } - + $distinct = ' distinct '; + $item_thread_top = ''; $sql_extra = " AND item.parent IN ( SELECT DISTINCT parent FROM item WHERE true $sql_options AND (( author_xchan IN ( $contact_str ) OR owner_xchan in ( $contact_str )) or allow_gid like '" . protect_sprintf('%<' . dbesc($group_hash) . '>%') . "' ) and id = parent $item_normal ) "; $x = group_rec_byhash(local_channel(), $group_hash); @@ -250,6 +254,8 @@ class Network extends \Zotlabs\Web\Controller { intval(local_channel()) ); if($r) { + $distinct = ' distinct '; + $item_thread_top = ''; $sql_extra = " AND item.parent IN ( SELECT DISTINCT parent FROM item WHERE true $sql_options AND uid = " . intval(local_channel()) . " AND ( author_xchan = '" . dbesc($r[0]['abook_xchan']) . "' or owner_xchan = '" . dbesc($r[0]['abook_xchan']) . "' ) $item_normal ) "; $title = replace_macros(get_markup_template("section_title.tpl"),array( '$title' => '' . urlencode($r[0]['xchan_name']) . ' ' . $r[0]['xchan_name'] . '' @@ -264,13 +270,15 @@ class Network extends \Zotlabs\Web\Controller { } } elseif($xchan) { - $r = q("select * from xchan where xchan_hash = '%s'", - dbesc($xchan) - ); - if($r) { - $sql_extra = " AND item.parent IN ( SELECT DISTINCT parent FROM item WHERE true $sql_options AND uid = " . intval(local_channel()) . " AND ( author_xchan = '" . dbesc($xchan) . "' or owner_xchan = '" . dbesc($xchan) . "' ) $item_normal ) "; - $title = replace_macros(get_markup_template("section_title.tpl"),array( - '$title' => '' . urlencode($r[0]['xchan_name']) . ' ' . $r[0]['xchan_name'] . '' + $r = q("select * from xchan where xchan_hash = '%s'", + dbesc($xchan) + ); + if($r) { + $distinct = ' distinct '; + $item_thread_top = ''; + $sql_extra = " AND item.parent IN ( SELECT DISTINCT parent FROM item WHERE true $sql_options AND uid = " . intval(local_channel()) . " AND ( author_xchan = '" . dbesc($xchan) . "' or owner_xchan = '" . dbesc($xchan) . "' ) $item_normal ) "; + $title = replace_macros(get_markup_template("section_title.tpl"),array( + '$title' => '' . urlencode($r[0]['xchan_name']) . ' ' . $r[0]['xchan_name'] . '' )); $o = $tabs; $o .= $title; @@ -373,6 +381,8 @@ class Network extends \Zotlabs\Web\Controller { } if($conv) { + $distinct = ' distinct '; + $item_thread_top = ''; $sql_extra .= sprintf(" AND parent IN (SELECT distinct(parent) from item where ( author_xchan like '%s' or item_mentionsme = 1 )) ", dbesc(protect_sprintf($channel['channel_hash'])) ); @@ -448,7 +458,7 @@ class Network extends \Zotlabs\Web\Controller { if($nouveau && $load) { // "New Item View" - show all items unthreaded in reverse created date order - $items = q("SELECT item.*, item.id AS item_id, received FROM item + $items = q("SELECT item.*, item.id AS item_id, received FROM item left join abook on ( item.owner_xchan = abook.abook_xchan $abook_uids ) $net_query WHERE true $uids $item_normal @@ -477,11 +487,11 @@ class Network extends \Zotlabs\Web\Controller { if($load) { // Fetch a page full of parent items for this page - $r = q("SELECT distinct item.id AS item_id, $ordering FROM item + $r = q("SELECT $distinct item.parent AS item_id FROM item left join abook on ( item.owner_xchan = abook.abook_xchan $abook_uids ) $net_query - WHERE true $uids $item_normal - AND item.parent = item.id + WHERE true $uids $item_thread_top $item_normal + AND item.mid = item.parent_mid and (abook.abook_blocked = 0 or abook.abook_flags is null) $sql_extra3 $sql_extra $sql_nets $net_query2 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'", diff --git a/Zotlabs/Module/Pubstream.php b/Zotlabs/Module/Pubstream.php index 2c25e2ce0..16a5fdbba 100644 --- a/Zotlabs/Module/Pubstream.php +++ b/Zotlabs/Module/Pubstream.php @@ -196,10 +196,10 @@ class Pubstream extends \Zotlabs\Web\Controller { } else { // Fetch a page full of parent items for this page - $r = q("SELECT distinct item.id AS item_id, $ordering FROM item + $r = q("SELECT item.id AS item_id FROM item left join abook on item.author_xchan = abook.abook_xchan $net_query - WHERE true $uids $item_normal + WHERE item_thread_top = 1 $uids $item_normal AND item.parent = item.id and (abook.abook_blocked = 0 or abook.abook_flags is null) $sql_extra3 $sql_extra $sql_nets $net_query2 diff --git a/Zotlabs/Widget/Forums.php b/Zotlabs/Widget/Forums.php index 44d95012f..b572444e3 100644 --- a/Zotlabs/Widget/Forums.php +++ b/Zotlabs/Widget/Forums.php @@ -64,7 +64,8 @@ class Forums { // There also should be a way to update this via ajax. for($x = 0; $x < count($r1); $x ++) { - $r = q("select sum(item_unseen) as unseen from item where owner_xchan = '%s' and uid = %d and item_unseen = 1 $perms_sql ", + $r = q("select sum(item_unseen) as unseen from item + where owner_xchan = '%s' and uid = %d and item_unseen = 1 $perms_sql ", dbesc($r1[$x]['xchan_hash']), intval(local_channel()) ); diff --git a/boot.php b/boot.php index 57d1d18f9..8eb6406b3 100755 --- a/boot.php +++ b/boot.php @@ -51,10 +51,10 @@ require_once('include/attach.php'); require_once('include/bbcode.php'); define ( 'PLATFORM_NAME', 'hubzilla' ); -define ( 'STD_VERSION', '3.1.5' ); +define ( 'STD_VERSION', '3.1.6' ); define ( 'ZOT_REVISION', '1.3' ); -define ( 'DB_UPDATE_VERSION', 1198 ); +define ( 'DB_UPDATE_VERSION', 1199 ); define ( 'PROJECT_BASE', __DIR__ ); diff --git a/install/update.php b/install/update.php index f7b9b03ed..1bb39fc65 100644 --- a/install/update.php +++ b/install/update.php @@ -1,6 +1,6 @@