From ac26b964663405521077c7710212a58c37a041c7 Mon Sep 17 00:00:00 2001 From: thomascube Date: Tue, 25 Mar 2008 19:08:19 +0000 Subject: Optimize database schema; get rid of unnecessary indexes --- SQL/mysql.initial.sql | 11 +++------- SQL/mysql.update-0.1a.sql | 51 ----------------------------------------------- SQL/mysql.update.sql | 22 +++++++------------- SQL/mysql5.initial.sql | 19 ++++++++---------- SQL/postgres.initial.sql | 2 +- SQL/postgres.update.sql | 14 +++---------- SQL/sqlite.initial.sql | 15 +++++--------- SQL/sqlite.update.sql | 12 +++++------ 8 files changed, 32 insertions(+), 114 deletions(-) delete mode 100644 SQL/mysql.update-0.1a.sql (limited to 'SQL') diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 926a83eab..ae195e438 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 @@ CREATE TABLE `cache` ( `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 @@ CREATE TABLE `messages` ( `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 60f22b3d6..000000000 --- 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 e904be278..70230784b 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 `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; + DROP INDEX `idx`, + DROP INDEX `uid`; -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; +ALTER TABLE `cache` + DROP INDEX `cache_key`, + 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 1a6ef1990..58e220f7d 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 @@ CREATE TABLE `session` ( `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 @@ CREATE TABLE `users` ( `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 @@ CREATE TABLE `messages` ( `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 @@ CREATE TABLE `cache` ( `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 @@ CREATE TABLE `contacts` ( 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 @@ CREATE TABLE `identities` ( 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 356a579fc..e8513af73 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -131,7 +131,7 @@ CREATE TABLE "cache" ( 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 0d63cf934..add454ab8 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 - -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; +-- RoundCube Webmail update script for Postgres databases +-- Updates from version 0.1-stable to 0.1.1 +ALTER TABLE "cache" ADD INDEX (user_id, cache_key); diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index 4ae752983..a1eaeb8be 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 @@ CREATE TABLE cache ( 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 @@ CREATE TABLE messages ( 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 e725729ad..ed0ad2012 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 @@ CREATE TABLE messages ( 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); + -- cgit v1.2.3