aboutsummaryrefslogtreecommitdiffstats
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
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
-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
-rw-r--r--Zotlabs/Widget/Forums.php3
-rwxr-xr-xboot.php4
-rw-r--r--install/update.php19
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' => '<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
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 @@
<?php
-define( 'UPDATE_VERSION' , 1198 );
+define( 'UPDATE_VERSION' , 1199 );
/**
*
@@ -3070,3 +3070,20 @@ function update_r1197() {
return UPDATE_SUCCESS;
}
+
+function update_r1198() {
+
+ if(ACTIVE_DBTYPE == DBTYPE_MYSQL) {
+ $r = q("ALTER TABLE item
+ DROP INDEX item_blocked,
+ DROP INDEX item_unpublished,
+ DROP INDEX item_deleted,
+ DROP INDEX item_delayed,
+ DROP INDEX item_hidden,
+ DROP INDEX item_pending_remove,
+ DROP INDEX item_type
+ ");
+ }
+
+ return UPDATE_SUCCESS;
+}