aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorNathan Guse <nathaniel.guse@gmail.com>2012-12-08 14:12:25 -0600
committerNathan Guse <nathaniel.guse@gmail.com>2012-12-08 14:12:25 -0600
commit0d6c8f46ffa1206e9d8d71fd30ef04e52f7cb2a8 (patch)
tree2a8e26649421aac826126d5cc2be49dcf8ba9d5d
parent2227ceab8bf0cccf95598f4e2a59cc7134adf7f0 (diff)
downloadforums-0d6c8f46ffa1206e9d8d71fd30ef04e52f7cb2a8.tar
forums-0d6c8f46ffa1206e9d8d71fd30ef04e52f7cb2a8.tar.gz
forums-0d6c8f46ffa1206e9d8d71fd30ef04e52f7cb2a8.tar.bz2
forums-0d6c8f46ffa1206e9d8d71fd30ef04e52f7cb2a8.tar.xz
forums-0d6c8f46ffa1206e9d8d71fd30ef04e52f7cb2a8.zip
[ticket/11103] Update indexes on notifications/user notifications tables
PHPBB3-11103
Diffstat
-rw-r--r--phpBB/develop/create_schema_files.php14
-rw-r--r--phpBB/install/database_update.php14
-rw-r--r--phpBB/install/schemas/firebird_schema.sql9
-rw-r--r--phpBB/install/schemas/mssql_schema.sql19
-rw-r--r--phpBB/install/schemas/mysql_40_schema.sql9
-rw-r--r--phpBB/install/schemas/mysql_41_schema.sql9
-rw-r--r--phpBB/install/schemas/oracle_schema.sql14
-rw-r--r--phpBB/install/schemas/postgres_schema.sql9
-rw-r--r--phpBB/install/schemas/sqlite_schema.sql9
9 files changed, 18 insertions, 88 deletions
diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php
index 3548bc5f05..87204f9e26 100644
--- a/phpBB/develop/create_schema_files.php
+++ b/phpBB/develop/create_schema_files.php
@@ -1309,13 +1309,8 @@ function get_schema_struct()
),
'PRIMARY_KEY' => 'notification_id',
'KEYS' => array(
- 'item_type' => array('INDEX', 'item_type'),
- 'item_id' => array('INDEX', 'item_id'),
- 'item_pid' => array('INDEX', 'item_parent_id'),
- 'user_id' => array('INDEX', 'user_id'),
- 'time' => array('INDEX', 'time'),
- 'unread' => array('INDEX', 'unread'),
- 'is_enabled' => array('INDEX', 'is_enabled'),
+ 'item_ident' => array('INDEX', array('item_type', 'item_id')),
+ 'user' => array('INDEX', array('user_id', 'unread')),
),
);
@@ -1791,11 +1786,6 @@ function get_schema_struct()
'user_id',
'method',
),
- 'KEYS' => array(
- 'it' => array('INDEX', 'item_type'),
- 'uid' => array('INDEX', 'user_id'),
- 'no' => array('INDEX', 'notify'),
- ),
);
$schema_data['phpbb_user_group'] = array(
diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php
index 33e8795327..a4739b7212 100644
--- a/phpBB/install/database_update.php
+++ b/phpBB/install/database_update.php
@@ -1122,13 +1122,8 @@ function database_update_info()
),
'PRIMARY_KEY' => 'notification_id',
'KEYS' => array(
- 'item_type' => array('INDEX', 'item_type'),
- 'item_id' => array('INDEX', 'item_id'),
- 'item_pid' => array('INDEX', 'item_parent_id'),
- 'user_id' => array('INDEX', 'user_id'),
- 'time' => array('INDEX', 'time'),
- 'unread' => array('INDEX', 'unread'),
- 'is_enabled' => array('INDEX', 'is_enabled'),
+ 'item_ident' => array('INDEX', array('item_type', 'item_id')),
+ 'user' => array('INDEX', array('user_id', 'unread')),
),
),
$table_prefix . 'user_notifications' => array(
@@ -1145,11 +1140,6 @@ function database_update_info()
'user_id',
'method',
),
- 'KEYS' => array(
- 'it' => array('INDEX', 'item_type'),
- 'uid' => array('INDEX', 'user_id'),
- 'no' => array('INDEX', 'notify'),
- ),
),
),
'add_columns' => array(
diff --git a/phpBB/install/schemas/firebird_schema.sql b/phpBB/install/schemas/firebird_schema.sql
index 8456958800..d3058fb0dd 100644
--- a/phpBB/install/schemas/firebird_schema.sql
+++ b/phpBB/install/schemas/firebird_schema.sql
@@ -633,13 +633,8 @@ CREATE TABLE phpbb_notifications (
ALTER TABLE phpbb_notifications ADD PRIMARY KEY (notification_id);;
-CREATE INDEX phpbb_notifications_item_type ON phpbb_notifications(item_type);;
-CREATE INDEX phpbb_notifications_item_id ON phpbb_notifications(item_id);;
-CREATE INDEX phpbb_notifications_item_pid ON phpbb_notifications(item_parent_id);;
-CREATE INDEX phpbb_notifications_user_id ON phpbb_notifications(user_id);;
-CREATE INDEX phpbb_notifications_time ON phpbb_notifications(time);;
-CREATE INDEX phpbb_notifications_unread ON phpbb_notifications(unread);;
-CREATE INDEX phpbb_notifications_is_enabled ON phpbb_notifications(is_enabled);;
+CREATE INDEX phpbb_notifications_item_ident ON phpbb_notifications(item_type, item_id);;
+CREATE INDEX phpbb_notifications_user ON phpbb_notifications(user_id, unread);;
CREATE GENERATOR phpbb_notifications_gen;;
SET GENERATOR phpbb_notifications_gen TO 0;;
diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql
index d0023aa411..377ff92620 100644
--- a/phpBB/install/schemas/mssql_schema.sql
+++ b/phpBB/install/schemas/mssql_schema.sql
@@ -774,25 +774,10 @@ ALTER TABLE [phpbb_notifications] WITH NOCHECK ADD
) ON [PRIMARY]
GO
-CREATE INDEX [item_type] ON [phpbb_notifications]([item_type]) ON [PRIMARY]
+CREATE INDEX [item_ident] ON [phpbb_notifications]([item_type], [item_id]) ON [PRIMARY]
GO
-CREATE INDEX [item_id] ON [phpbb_notifications]([item_id]) ON [PRIMARY]
-GO
-
-CREATE INDEX [item_pid] ON [phpbb_notifications]([item_parent_id]) ON [PRIMARY]
-GO
-
-CREATE INDEX [user_id] ON [phpbb_notifications]([user_id]) ON [PRIMARY]
-GO
-
-CREATE INDEX [time] ON [phpbb_notifications]([time]) ON [PRIMARY]
-GO
-
-CREATE INDEX [unread] ON [phpbb_notifications]([unread]) ON [PRIMARY]
-GO
-
-CREATE INDEX [is_enabled] ON [phpbb_notifications]([is_enabled]) ON [PRIMARY]
+CREATE INDEX [user] ON [phpbb_notifications]([user_id], [unread]) ON [PRIMARY]
GO
diff --git a/phpBB/install/schemas/mysql_40_schema.sql b/phpBB/install/schemas/mysql_40_schema.sql
index 07e529e833..4ceb664cd3 100644
--- a/phpBB/install/schemas/mysql_40_schema.sql
+++ b/phpBB/install/schemas/mysql_40_schema.sql
@@ -442,13 +442,8 @@ CREATE TABLE phpbb_notifications (
time int(11) UNSIGNED DEFAULT '1' NOT NULL,
data blob NOT NULL,
PRIMARY KEY (notification_id),
- KEY item_type (item_type),
- KEY item_id (item_id),
- KEY item_pid (item_parent_id),
- KEY user_id (user_id),
- KEY time (time),
- KEY unread (unread),
- KEY is_enabled (is_enabled)
+ KEY item_ident (item_type, item_id),
+ KEY user (user_id, unread)
);
diff --git a/phpBB/install/schemas/mysql_41_schema.sql b/phpBB/install/schemas/mysql_41_schema.sql
index 361f53c313..423b97567a 100644
--- a/phpBB/install/schemas/mysql_41_schema.sql
+++ b/phpBB/install/schemas/mysql_41_schema.sql
@@ -442,13 +442,8 @@ CREATE TABLE phpbb_notifications (
time int(11) UNSIGNED DEFAULT '1' NOT NULL,
data text NOT NULL,
PRIMARY KEY (notification_id),
- KEY item_type (item_type),
- KEY item_id (item_id),
- KEY item_pid (item_parent_id),
- KEY user_id (user_id),
- KEY time (time),
- KEY unread (unread),
- KEY is_enabled (is_enabled)
+ KEY item_ident (item_type, item_id),
+ KEY user (user_id, unread)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;
diff --git a/phpBB/install/schemas/oracle_schema.sql b/phpBB/install/schemas/oracle_schema.sql
index a2bb016dae..b3ea3c7d5e 100644
--- a/phpBB/install/schemas/oracle_schema.sql
+++ b/phpBB/install/schemas/oracle_schema.sql
@@ -857,19 +857,9 @@ CREATE TABLE phpbb_notifications (
)
/
-CREATE INDEX phpbb_notifications_item_type ON phpbb_notifications (item_type)
+CREATE INDEX phpbb_notifications_item_ident ON phpbb_notifications (item_type, item_id)
/
-CREATE INDEX phpbb_notifications_item_id ON phpbb_notifications (item_id)
-/
-CREATE INDEX phpbb_notifications_item_pid ON phpbb_notifications (item_parent_id)
-/
-CREATE INDEX phpbb_notifications_user_id ON phpbb_notifications (user_id)
-/
-CREATE INDEX phpbb_notifications_time ON phpbb_notifications (time)
-/
-CREATE INDEX phpbb_notifications_unread ON phpbb_notifications (unread)
-/
-CREATE INDEX phpbb_notifications_is_enabled ON phpbb_notifications (is_enabled)
+CREATE INDEX phpbb_notifications_user ON phpbb_notifications (user_id, unread)
/
CREATE SEQUENCE phpbb_notifications_seq
diff --git a/phpBB/install/schemas/postgres_schema.sql b/phpBB/install/schemas/postgres_schema.sql
index 94926d0b7a..e43b64468d 100644
--- a/phpBB/install/schemas/postgres_schema.sql
+++ b/phpBB/install/schemas/postgres_schema.sql
@@ -614,13 +614,8 @@ CREATE TABLE phpbb_notifications (
PRIMARY KEY (notification_id)
);
-CREATE INDEX phpbb_notifications_item_type ON phpbb_notifications (item_type);
-CREATE INDEX phpbb_notifications_item_id ON phpbb_notifications (item_id);
-CREATE INDEX phpbb_notifications_item_pid ON phpbb_notifications (item_parent_id);
-CREATE INDEX phpbb_notifications_user_id ON phpbb_notifications (user_id);
-CREATE INDEX phpbb_notifications_time ON phpbb_notifications (time);
-CREATE INDEX phpbb_notifications_unread ON phpbb_notifications (unread);
-CREATE INDEX phpbb_notifications_is_enabled ON phpbb_notifications (is_enabled);
+CREATE INDEX phpbb_notifications_item_ident ON phpbb_notifications (item_type, item_id);
+CREATE INDEX phpbb_notifications_user ON phpbb_notifications (user_id, unread);
/*
Table: 'phpbb_poll_options'
diff --git a/phpBB/install/schemas/sqlite_schema.sql b/phpBB/install/schemas/sqlite_schema.sql
index 43611ef70b..e3b556668d 100644
--- a/phpBB/install/schemas/sqlite_schema.sql
+++ b/phpBB/install/schemas/sqlite_schema.sql
@@ -430,13 +430,8 @@ CREATE TABLE phpbb_notifications (
data text(65535) NOT NULL DEFAULT ''
);
-CREATE INDEX phpbb_notifications_item_type ON phpbb_notifications (item_type);
-CREATE INDEX phpbb_notifications_item_id ON phpbb_notifications (item_id);
-CREATE INDEX phpbb_notifications_item_pid ON phpbb_notifications (item_parent_id);
-CREATE INDEX phpbb_notifications_user_id ON phpbb_notifications (user_id);
-CREATE INDEX phpbb_notifications_time ON phpbb_notifications (time);
-CREATE INDEX phpbb_notifications_unread ON phpbb_notifications (unread);
-CREATE INDEX phpbb_notifications_is_enabled ON phpbb_notifications (is_enabled);
+CREATE INDEX phpbb_notifications_item_ident ON phpbb_notifications (item_type, item_id);
+CREATE INDEX phpbb_notifications_user ON phpbb_notifications (user_id, unread);
# Table: 'phpbb_poll_options'
CREATE TABLE phpbb_poll_options (
generated by cgit v1.2.1 (git 2.21.0) at 2025-08-08 19:46:24 +0000