aboutsummaryrefslogtreecommitdiffstats
path: root/Zotlabs/Module
diff options
context:
space:
mode:
authorMario Vavti <mario@mariovavti.com>2018-02-04 20:42:40 +0100
committerMario Vavti <mario@mariovavti.com>2018-02-04 20:42:40 +0100
commit3cc756f3029e5f3b02008dac94a90e168d398c9a (patch)
treebc6b6cdc552ef119e0f7beb8b40e83ffe59798c7 /Zotlabs/Module
parent512f3a764361dde44e36fb72c105265d6df298ad (diff)
downloadvolse-hubzilla-3cc756f3029e5f3b02008dac94a90e168d398c9a.tar.gz
volse-hubzilla-3cc756f3029e5f3b02008dac94a90e168d398c9a.tar.bz2
volse-hubzilla-3cc756f3029e5f3b02008dac94a90e168d398c9a.zip
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
Diffstat (limited to 'Zotlabs/Module')
-rw-r--r--Zotlabs/Module/Channel.php6
-rw-r--r--Zotlabs/Module/Hq.php4
-rw-r--r--Zotlabs/Module/Network.php36
-rw-r--r--Zotlabs/Module/Ping.php19
-rw-r--r--Zotlabs/Module/Pubstream.php4
5 files changed, 37 insertions, 32 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' => '<a href="' . zid($r[0]['xchan_url']) . '" ><img src="' . zid($r[0]['xchan_photo_s']) . '" alt="' . urlencode($r[0]['xchan_name']) . '" /></a> <a href="' . zid($r[0]['xchan_url']) . '" >' . $r[0]['xchan_name'] . '</a>'
@@ -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' => '<a href="' . zid($r[0]['xchan_url']) . '" ><img src="' . zid($r[0]['xchan_photo_s']) . '" alt="' . urlencode($r[0]['xchan_name']) . '" /></a> <a href="' . zid($r[0]['xchan_url']) . '" >' . $r[0]['xchan_name'] . '</a>'
+ $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' => '<a href="' . zid($r[0]['xchan_url']) . '" ><img src="' . zid($r[0]['xchan_photo_s']) . '" alt="' . urlencode($r[0]['xchan_name']) . '" /></a> <a href="' . zid($r[0]['xchan_url']) . '" >' . $r[0]['xchan_name'] . '</a>'
));
$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