From ac26b964663405521077c7710212a58c37a041c7 Mon Sep 17 00:00:00 2001
From: thomascube <thomas@roundcube.net>
Date: Tue, 25 Mar 2008 15:08:19 -0400
Subject: [PATCH] Optimize database schema; get rid of unnecessary indexes
---
/dev/null | 51 -----------------
SQL/mysql5.initial.sql | 19 ++---
SQL/sqlite.update.sql | 12 +--
SQL/postgres.update.sql | 14 +---
SQL/mysql.update.sql | 22 ++-----
SQL/postgres.initial.sql | 2
SQL/mysql.initial.sql | 11 +--
SQL/sqlite.initial.sql | 15 +---
8 files changed, 32 insertions(+), 114 deletions(-)
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 926a83e..ae195e4 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail initial database structure
--- Version 0.1-rc1
+-- Version 0.1
--
-- --------------------------------------------------------
@@ -16,9 +16,7 @@
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`data` longtext NOT NULL,
PRIMARY KEY (`cache_id`),
- KEY `user_id` (`user_id`),
- KEY `cache_key` (`cache_key`),
- KEY `session_id` (`session_id`)
+ INDEX `user_cache_index` (`user_id`,`cache_key`)
);
-- --------------------------------------------------------
@@ -118,10 +116,7 @@
`size` int(11) unsigned NOT NULL default '0',
`headers` text NOT NULL,
`structure` text,
- PRIMARY KEY (`message_id`),
- KEY `user_id` (`user_id`),
- KEY `idx` (`idx`),
- KEY `uid` (`uid`),
+ PRIMARY KEY (`message_id`),
UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`)
);
diff --git a/SQL/mysql.update-0.1a.sql b/SQL/mysql.update-0.1a.sql
deleted file mode 100644
index 60f22b3..0000000
--- a/SQL/mysql.update-0.1a.sql
+++ /dev/null
@@ -1,51 +0,0 @@
--- RoundCube Webmail update script for MySQL databases
--- Updates from version 0.1-20051007
-
-
-ALTER TABLE `session` ADD `ip` VARCHAR(40) NOT NULL AFTER changed;
-ALTER TABLE `users` ADD `alias` VARCHAR(128) NOT NULL AFTER mail_host;
-
-
-
--- RoundCube Webmail update script for MySQL databases
--- Updates from version 0.1-20051021
-
-ALTER TABLE `session` CHANGE `sess_id` `sess_id` VARCHAR(40) NOT NULL;
-
-ALTER TABLE `contacts` CHANGE `del` `del` TINYINT(1) NOT NULL;
-ALTER TABLE `contacts` ADD `changed` DATETIME NOT NULL AFTER `user_id`;
-
-UPDATE `contacts` SET `del`=0 WHERE `del`=1;
-UPDATE `contacts` SET `del`=1 WHERE `del`=2;
-
-ALTER TABLE `identities` CHANGE `default` `standard` TINYINT(1) NOT NULL;
-ALTER TABLE `identities` CHANGE `del` `del` TINYINT(1) NOT NULL;
-
-UPDATE `identities` SET `del`=0 WHERE `del`=1;
-UPDATE `identities` SET `del`=1 WHERE `del`=2;
-UPDATE `identities` SET `standard`=0 WHERE `standard`=1;
-UPDATE `identities` SET `standard`=1 WHERE `standard`=2;
-
-CREATE TABLE `messages` (
- `message_id` int(11) unsigned NOT NULL auto_increment,
- `user_id` int(11) unsigned NOT NULL default '0',
- `del` tinyint(1) NOT NULL default '0',
- `cache_key` varchar(128) NOT NULL default '',
- `created` datetime NOT NULL default '0000-00-00 00:00:00',
- `idx` int(11) unsigned NOT NULL default '0',
- `uid` int(11) unsigned NOT NULL default '0',
- `subject` varchar(255) NOT NULL default '',
- `from` varchar(255) NOT NULL default '',
- `to` varchar(255) NOT NULL default '',
- `cc` varchar(255) NOT NULL default '',
- `date` datetime NOT NULL default '0000-00-00 00:00:00',
- `size` int(11) unsigned NOT NULL default '0',
- `headers` text NOT NULL,
- `structure` text,
- PRIMARY KEY (`message_id`),
- KEY `user_id` (`user_id`),
- KEY `idx` (`idx`),
- KEY `uid` (`uid`),
- UNIQUE `uniqueness` (`cache_key`, `uid`)
-) TYPE=MyISAM;
-
diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql
index e904be2..7023078 100644
--- a/SQL/mysql.update.sql
+++ b/SQL/mysql.update.sql
@@ -1,21 +1,13 @@
-- RoundCube Webmail update script for MySQL databases
--- Updates from version 0.1-beta and 0.1-beta2
+-- Updates from version 0.1-stable to 0.1.1
TRUNCATE TABLE `messages`;
ALTER TABLE `messages`
- DROP `body`,
+ DROP INDEX `idx`,
+ DROP INDEX `uid`;
+
+ALTER TABLE `cache`
DROP INDEX `cache_key`,
- ADD `structure` TEXT,
- ADD UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`);
-
-ALTER TABLE `identities`
- ADD `html_signature` tinyint(1) default 0 NOT NULL;
-
-ALTER TABLE `session` CHANGE `ip` `ip` VARCHAR(40)
-
--- Uncomment these lines if you're using MySQL 4.1 or higher
--- ALTER TABLE `users`
--- DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci,
--- CHANGE `username` `username` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
--- CHANGE `alias` `alias` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
+ DROP INDEX `session_id`,
+ ADD INDEX `user_cache_index` (`user_id`,`cache_key`);
diff --git a/SQL/mysql5.initial.sql b/SQL/mysql5.initial.sql
index 1a6ef19..58e220f 100644
--- a/SQL/mysql5.initial.sql
+++ b/SQL/mysql5.initial.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail initial database structure
--- Version 0.1-rc1
+-- Version 0.1
-- --------------------------------------------------------
@@ -15,7 +15,7 @@
`ip` varchar(40) NOT NULL,
`vars` text NOT NULL,
PRIMARY KEY(`sess_id`)
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Table structure for table `users`
@@ -30,7 +30,7 @@
`language` varchar(5) NOT NULL DEFAULT 'en',
`preferences` text,
PRIMARY KEY(`user_id`)
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Table structure for table `messages`
@@ -52,14 +52,12 @@
`headers` text NOT NULL,
`structure` text,
PRIMARY KEY(`message_id`),
- INDEX `idx`(`idx`),
- INDEX `uid`(`uid`),
UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`),
CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Table structure for table `cache`
@@ -72,13 +70,12 @@
`data` longtext NOT NULL,
`user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY(`cache_id`),
- INDEX `cache_key`(`cache_key`),
- INDEX `session_id`(`session_id`),
+ INDEX `user_cache_index` (`user_id`,`cache_key`),
CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Table structure for table `contacts`
@@ -98,7 +95,7 @@
REFERENCES `users`(`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Table structure for table `identities`
@@ -120,7 +117,7 @@
REFERENCES `users`(`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
SET FOREIGN_KEY_CHECKS=1;
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index 356a579..e8513af 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -131,7 +131,7 @@
data text NOT NULL
);
-
+ALTER TABLE "cache" ADD INDEX (user_id, cache_key);
--
-- Sequence "message_ids"
diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql
index 0d63cf9..add454a 100644
--- a/SQL/postgres.update.sql
+++ b/SQL/postgres.update.sql
@@ -1,12 +1,4 @@
--- RoundCube Webmail update script for Postres databases
--- Updates from version 0.1-beta and older
+-- RoundCube Webmail update script for Postgres databases
+-- Updates from version 0.1-stable to 0.1.1
-ALTER TABLE "messages" DROP body;
-ALTER TABLE "messages" ADD structure TEXT;
-ALTER TABLE "messages" ADD UNIQUE (user_id, cache_key, uid);
-
-ALTER TABLE "identities" ADD html_signature INTEGER;
-ALTER TABLE "identities" ALTER html_signature SET DEFAULT 0;
-UPDATE identities SET html_signature = 0;
-ALTER TABLE "identities" ALTER html_signature SET NOT NULL;
-
+ALTER TABLE "cache" ADD INDEX (user_id, cache_key);
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index 4ae7529..a1eaeb8 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail initial database structure
--- Version 0.1-rc1
+-- Version 0.1
--
-- --------------------------------------------------------
@@ -17,9 +17,7 @@
data longtext NOT NULL
);
-CREATE INDEX ix_cache_user_id ON cache(user_id);
-CREATE INDEX ix_cache_cache_key ON cache(cache_key);
-CREATE INDEX ix_cache_session_id ON cache(session_id);
+CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
-- --------------------------------------------------------
@@ -115,14 +113,11 @@
subject varchar(255) NOT NULL default '',
"from" varchar(255) NOT NULL default '',
"to" varchar(255) NOT NULL default '',
- cc varchar(255) NOT NULL default '',
- date datetime NOT NULL default '0000-00-00 00:00:00',
+ "cc" varchar(255) NOT NULL default '',
+ "date" datetime NOT NULL default '0000-00-00 00:00:00',
size integer NOT NULL default '0',
headers text NOT NULL,
structure text
);
-CREATE INDEX ix_messages_user_id ON messages(user_id);
-CREATE INDEX ix_messages_cache_key ON messages(cache_key);
-CREATE INDEX ix_messages_idx ON messages(idx);
-CREATE INDEX ix_messages_uid ON messages(uid);
+CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid);
diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql
index e725729..ed0ad20 100644
--- a/SQL/sqlite.update.sql
+++ b/SQL/sqlite.update.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail update script for SQLite databases
--- Updates from version 0.1-beta2 and older
+-- Updates from version 0.1-stable to 0.1.1
DROP TABLE messages;
@@ -14,14 +14,12 @@
subject varchar(255) NOT NULL default '',
"from" varchar(255) NOT NULL default '',
"to" varchar(255) NOT NULL default '',
- cc varchar(255) NOT NULL default '',
- date datetime NOT NULL default '0000-00-00 00:00:00',
+ "cc" varchar(255) NOT NULL default '',
+ "date" datetime NOT NULL default '0000-00-00 00:00:00',
size integer NOT NULL default '0',
headers text NOT NULL,
structure text
);
-CREATE INDEX ix_messages_user_id ON messages(user_id);
-CREATE INDEX ix_messages_cache_key ON messages(cache_key);
-CREATE INDEX ix_messages_idx ON messages(idx);
-CREATE INDEX ix_messages_uid ON messages(uid);
+CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid);
+
--
Gitblit v1.9.1