From 661c20e452255754bbccadc3b29dc3c598c07ed0 Mon Sep 17 00:00:00 2001 From: Mario Vavti Date: Thu, 8 Feb 2018 09:47:49 +0100 Subject: more db and queries finetuning --- Zotlabs/Module/Ping.php | 26 +++++++++++++------------- boot.php | 4 ++-- install/schema_mysql.sql | 4 ++-- install/update.php | 16 +++++++++++++++- 4 files changed, 32 insertions(+), 18 deletions(-) diff --git a/Zotlabs/Module/Ping.php b/Zotlabs/Module/Ping.php index 2e86804ac..96ade22c0 100644 --- a/Zotlabs/Module/Ping.php +++ b/Zotlabs/Module/Ping.php @@ -148,8 +148,8 @@ class Ping extends \Zotlabs\Web\Controller { $pubs = q("SELECT count(id) as total from item WHERE uid = %d - AND author_xchan != '%s' AND item_unseen = 1 + AND author_xchan != '%s' AND created > '" . datetime_convert('UTC','UTC',$_SESSION['static_loadtime']) . "' $item_normal", intval($sys['channel_id']), @@ -166,8 +166,8 @@ class Ping extends \Zotlabs\Web\Controller { $r = q("SELECT * FROM item WHERE uid = %d - AND author_xchan != '%s' AND item_unseen = 1 + AND author_xchan != '%s' AND created > '" . datetime_convert('UTC','UTC',$_SESSION['static_loadtime']) . "' $item_normal ORDER BY created DESC @@ -208,22 +208,22 @@ class Ping extends \Zotlabs\Web\Controller { if(x($_REQUEST, 'markRead') && local_channel()) { switch($_REQUEST['markRead']) { case 'network': - $r = q("update item set item_unseen = 0 where item_unseen = 1 and uid = %d", + $r = q("UPDATE item SET item_unseen = 0 WHERE uid = %d AND item_unseen = 1", intval(local_channel()) ); break; case 'home': - $r = q("update item set item_unseen = 0 where item_unseen = 1 and item_wall = 1 and uid = %d", + $r = q("UPDATE item SET item_unseen = 0 WHERE uid = %d AND item_unseen = 1 AND item_wall = 1", intval(local_channel()) ); break; case 'mail': - $r = q("update mail set mail_seen = 1 where mail_seen = 0 and channel_id = %d ", + $r = q("UPDATE mail SET mail_seen = 1 WHERE channel_id = %d AND mail_seen = 0", intval(local_channel()) ); break; case 'all_events': - $r = q("update event set dismissed = 1 where dismissed = 0 and uid = %d AND dtstart < '%s' AND dtstart > '%s' ", + $r = q("UPDATE event SET dismissed = 1 WHERE uid = %d AND dismissed = 0 AND dtstart < '%s' AND dtstart > '%s' ", intval(local_channel()), dbesc(datetime_convert('UTC', date_default_timezone_get(), 'now + ' . intval($evdays) . ' days')), dbesc(datetime_convert('UTC', date_default_timezone_get(), 'now - 1 days')) @@ -243,9 +243,9 @@ class Ping extends \Zotlabs\Web\Controller { } if(x($_REQUEST, 'markItemRead') && local_channel()) { - $r = q("update item set item_unseen = 0 where parent = %d and uid = %d", - intval($_REQUEST['markItemRead']), - intval(local_channel()) + $r = q("UPDATE item SET item_unseen = 0 WHERE uid = %d AND parent = %d", + intval(local_channel()), + intval($_REQUEST['markItemRead']) ); } @@ -254,7 +254,7 @@ class Ping extends \Zotlabs\Web\Controller { * dropdown menu. */ if(argc() > 1 && argv(1) === 'notify') { - $t = q("select * from notify where uid = %d and seen = 0 order by created desc", + $t = q("SELECT * FROM notify WHERE uid = %d AND seen = 0 ORDER BY CREATED DESC", intval(local_channel()) ); @@ -320,10 +320,10 @@ class Ping extends \Zotlabs\Web\Controller { $r = q("SELECT * FROM item WHERE uid = %d - AND author_xchan != '%s' AND item_unseen = 1 + AND author_xchan != '%s' $item_normal - ORDER BY created DESC, id + ORDER BY created DESC LIMIT 300", intval(local_channel()), dbesc($ob_hash) @@ -495,7 +495,7 @@ class Ping extends \Zotlabs\Web\Controller { if($vnotify & (VNOTIFY_NETWORK|VNOTIFY_CHANNEL)) { $r = q("SELECT id, item_wall FROM item - WHERE item_unseen = 1 and uid = %d + WHERE uid = %d and item_unseen = 1 $item_normal AND author_xchan != '%s'", intval(local_channel()), diff --git a/boot.php b/boot.php index 184ff6ea2..7c6fd090c 100755 --- a/boot.php +++ b/boot.php @@ -50,10 +50,10 @@ require_once('include/attach.php'); require_once('include/bbcode.php'); define ( 'PLATFORM_NAME', 'hubzilla' ); -define ( 'STD_VERSION', '3.1.7' ); +define ( 'STD_VERSION', '3.1.8' ); define ( 'ZOT_REVISION', '1.3' ); -define ( 'DB_UPDATE_VERSION', 1200 ); +define ( 'DB_UPDATE_VERSION', 1201 ); define ( 'PROJECT_BASE', __DIR__ ); diff --git a/install/schema_mysql.sql b/install/schema_mysql.sql index ea13e0de6..aa0ea0178 100644 --- a/install/schema_mysql.sql +++ b/install/schema_mysql.sql @@ -651,6 +651,7 @@ CREATE TABLE IF NOT EXISTS `item` ( KEY `uid_commented` (`uid`, `commented`), KEY `uid_created` (`uid`, `created`), KEY `uid_item_unseen` (`uid`, `item_unseen`), + KEY `uid_item_type` (`uid`, `item_type`), KEY `aid` (`aid`), KEY `owner_xchan` (`owner_xchan`), KEY `author_xchan` (`author_xchan`), @@ -689,8 +690,7 @@ CREATE TABLE IF NOT EXISTS `item` ( KEY `item_verified` (`item_verified`), KEY `item_retained` (`item_retained`), KEY `item_rss` (`item_rss`), - KEY `item_consensus` (`item_consensus`), - KEY `item_type` (`item_type`) + KEY `item_consensus` (`item_consensus`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS `item_id` ( diff --git a/install/update.php b/install/update.php index ceaccdfa3..d5ad394f4 100644 --- a/install/update.php +++ b/install/update.php @@ -1,6 +1,6 @@