diff options
author | Mario Vavti <mario@mariovavti.com> | 2019-03-19 10:52:10 +0100 |
---|---|---|
committer | Mario Vavti <mario@mariovavti.com> | 2019-03-19 10:52:10 +0100 |
commit | fad26a49beb9d067ad101798f4c0b2b15dcb035c (patch) | |
tree | a9ab66859b9a16f6bd4f02a034c3e655f92eba46 | |
parent | 1742159345dbfd9cc66b03710c9bcc3fc5f93378 (diff) | |
download | volse-hubzilla-fad26a49beb9d067ad101798f4c0b2b15dcb035c.tar.gz volse-hubzilla-fad26a49beb9d067ad101798f4c0b2b15dcb035c.tar.bz2 volse-hubzilla-fad26a49beb9d067ad101798f4c0b2b15dcb035c.zip |
mysql: remove some unused indices and add thr_parent index. pgsql: sync indices with mysql indices after testing on hub.disroot.org and add thr_parent
-rw-r--r-- | Zotlabs/Update/_1231.php | 73 | ||||
-rwxr-xr-x | boot.php | 2 | ||||
-rw-r--r-- | install/schema_mysql.sql | 7 | ||||
-rw-r--r-- | install/schema_postgres.sql | 32 |
4 files changed, 89 insertions, 25 deletions
diff --git a/Zotlabs/Update/_1231.php b/Zotlabs/Update/_1231.php new file mode 100644 index 000000000..a685c5b28 --- /dev/null +++ b/Zotlabs/Update/_1231.php @@ -0,0 +1,73 @@ +<?php + +namespace Zotlabs\Update; + +class _1231 { + + function run() { + + q("START TRANSACTION"); + + if(ACTIVE_DBTYPE == DBTYPE_POSTGRES) { + $r1 = q("DROP INDEX item_uid"); + $r2 = q("DROP INDEX item_aid"); + $r3 = q("DROP INDEX item_restrict"); + $r4 = q("DROP INDEX item_flags"); + $r5 = q("DROP INDEX item_private"); + $r6 = q("DROP INDEX item_starred"); + $r7 = q("DROP INDEX item_thread_top"); + $r8 = q("DROP INDEX item_retained"); + $r9 = q("DROP INDEX item_deleted"); + $r10 = q("DROP INDEX item_type"); + $r11 = q("DROP INDEX item_hidden"); + $r12 = q("DROP INDEX item_unpublished"); + $r13 = q("DROP INDEX item_delayed"); + $r14 = q("DROP INDEX item_pending_remove"); + $r15 = q("DROP INDEX item_blocked"); + $r16 = q("DROP INDEX item_unseen"); + $r17 = q("DROP INDEX item_relay"); + $r18 = q("DROP INDEX item_verified"); + $r19 = q("DROP INDEX item_notshown"); + + $r20 = q("create index item_uid_item_type on item (uid, item_type)"); + $r21 = q("create index item_uid_item_thread_top on item (uid, item_thread_top)"); + $r22 = q("create index item_uid_item_blocked on item (uid, item_blocked)"); + $r23 = q("create index item_uid_item_wall on item (uid, item_wall)"); + $r24 = q("create index item_uid_item_starred on item (uid, item_starred)"); + $r25 = q("create index item_uid_item_retained on item (uid, item_retained)"); + $r26 = q("create index item_uid_item_private on item (uid, item_private)"); + $r27 = q("create index item_uid_resource_type on item (uid, resource_type)"); + $r28 = q("create index item_item_deleted_item_pending_remove_changed on item (item_deleted, item_pending_remove, changed)"); + $r29 = q("create index item_item_pending_remove_changed on item (item_pending_remove, changed)"); + + $r30 = q("create index item_thr_parent on item (thr_parent)"); + + $r = ( + $r1 && $r2 && $r3 && $r4 && $r5 && $r6 && $r7 && $r8 && $r9 && $r10 && $r11 && $r12 && $r13 && $r14 + && $r15 && $r16 && $r17 && $r18 && $r19 && $r20 && $r21 && $r22 && $r23 && $r24 && $r25 && $r26 + && $r27 && $r28 && $r29 && $r30 + ); + } + else { + + $r1 = q("ALTER TABLE item DROP INDEX item_unseen"); + $r2 = q("ALTER TABLE item DROP INDEX item_relay"); + $r3 = q("ALTER TABLE item DROP INDEX item_verified"); + $r4 = q("ALTER TABLE item DROP INDEX item_notshown"); + + $r5 = q("ALTER TABLE item ADD INDEX thr_parent (thr_parent)"); + + $r = ($r1 && $r2 && $r3 && $r4 && $r5); + } + + if($r) { + q("COMMIT"); + return UPDATE_SUCCESS; + } + + q("ROLLBACK"); + return UPDATE_FAILED; + + } + +} @@ -53,7 +53,7 @@ define ( 'PLATFORM_NAME', 'hubzilla' ); define ( 'STD_VERSION', '4.1' ); define ( 'ZOT_REVISION', '6.0a' ); -define ( 'DB_UPDATE_VERSION', 1230 ); +define ( 'DB_UPDATE_VERSION', 1231 ); define ( 'PROJECT_BASE', __DIR__ ); diff --git a/install/schema_mysql.sql b/install/schema_mysql.sql index 49a2c476a..cb4a3fa74 100644 --- a/install/schema_mysql.sql +++ b/install/schema_mysql.sql @@ -691,19 +691,16 @@ CREATE TABLE IF NOT EXISTS `item` ( KEY `changed` (`changed`), KEY `item_origin` (`item_origin`), KEY `item_wall` (`item_wall`), - KEY `item_unseen` (`item_unseen`), KEY `item_uplink` (`item_uplink`), - KEY `item_notshown` (`item_notshown`), KEY `item_nsfw` (`item_nsfw`), - KEY `item_relay` (`item_relay`), KEY `item_mentionsme` (`item_mentionsme`), KEY `item_nocomment` (`item_nocomment`), KEY `item_obscured` (`item_obscured`), - KEY `item_verified` (`item_verified`), KEY `item_rss` (`item_rss`), KEY `item_consensus` (`item_consensus`), KEY `item_deleted_pending_remove_changed` (`item_deleted`, `item_pending_remove`, `changed`), - KEY `item_pending_remove_changed` (`item_pending_remove`, `changed`) + KEY `item_pending_remove_changed` (`item_pending_remove`, `changed`), + KEY `thr_parent` (`thr_parent`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS `item_id` ( diff --git a/install/schema_postgres.sql b/install/schema_postgres.sql index 1b6cab51b..e56e054b0 100644 --- a/install/schema_postgres.sql +++ b/install/schema_postgres.sql @@ -627,7 +627,6 @@ CREATE TABLE "item" ( "item_search_vector" tsvector, PRIMARY KEY ("id") ); -create index "item_uid" on item ("uid"); create index "item_uuid" on item ("uuid"); create index "item_parent" on item ("parent"); create index "item_created" on item ("created"); @@ -638,17 +637,13 @@ create index "item_uid_created" on item ("uid","created"); create index "item_uid_unseen" on item ("uid","item_unseen"); create index "item_changed" on item ("changed"); create index "item_comments_closed" on item ("comments_closed"); -create index "item_aid" on item ("aid"); create index "item_owner_xchan" on item ("owner_xchan"); create index "item_author_xchan" on item ("author_xchan"); create index "item_resource_id" on item ("resource_id"); create index "item_resource_type" on item ("resource_type"); -create index "item_restrict" on item ("item_restrict"); -create index "item_flags" on item ("item_flags"); create index "item_commented" on item ("commented"); create index "item_verb" on item ("verb"); create index "item_obj_type" on item ("obj_type"); -create index "item_private" on item ("item_private"); create index "item_llink" on item ("llink"); create index "item_expires" on item ("expires"); create index "item_revision" on item ("revision"); @@ -659,30 +654,29 @@ create index "item_uid_mid" on item ("mid","uid"); create index "item_public_policy" on item ("public_policy"); create index "item_comment_policy" on item ("comment_policy"); create index "item_layout_mid" on item ("layout_mid"); -create index "item_unseen" on item ("item_unseen"); create index "item_wall" on item ("item_wall"); create index "item_origin" on item ("item_origin"); -create index "item_starred" on item ("item_starred"); create index "item_uplink" on item ("item_uplink"); create index "item_consensus" on item ("item_consensus"); -create index "item_thread_top" on item ("item_thread_top"); -create index "item_notshown" on item ("item_notshown"); create index "item_nsfw" on item ("item_nsfw"); -create index "item_relay" on item ("item_relay"); create index "item_mentionsme" on item ("item_mentionsme"); create index "item_nocomment" on item ("item_nocomment"); create index "item_obscured" on item ("item_obscured"); -create index "item_verified" on item ("item_verified"); -create index "item_retained" on item ("item_retained"); create index "item_rss" on item ("item_rss"); -create index "item_deleted" on item ("item_deleted"); -create index "item_type" on item ("item_type"); -create index "item_hidden" on item ("item_hidden"); -create index "item_unpublished" on item ("item_unpublished"); -create index "item_delayed" on item ("item_delayed"); -create index "item_pending_remove" on item ("item_pending_remove"); -create index "item_blocked" on item ("item_blocked"); +create index "item_thr_parent" on item ("thr_parent"); + +create index "item_uid_item_type" on item ("uid", "item_type"); +create index "item_uid_item_thread_top" on item ("uid", "item_thread_top"); +create index "item_uid_item_blocked" on item ("uid", "item_blocked"); +create index "item_uid_item_wall" on item ("uid", "item_wall"); +create index "item_uid_item_starred" on item ("uid", "item_starred"); +create index "item_uid_item_retained" on item ("uid", "item_retained"); +create index "item_uid_item_private" on item ("uid", "item_private"); +create index "item_uid_resource_type" on item ("uid", "resource_type"); +create index "item_item_deleted_item_pending_remove_changed" on item ("item_deleted", "item_pending_remove", "changed"); +create index "item_item_pending_remove_changed" on item ("item_pending_remove", "changed"); + -- fulltext indexes create index "item_search_idx" on item USING gist("item_search_vector"); create index "item_allow_cid" on item ("allow_cid"); |