Optimize database schema; get rid of unnecessary indexes
1 files deleted
7 files modified
| | |
| | | -- RoundCube Webmail initial database structure |
| | | -- Version 0.1-rc1 |
| | | -- Version 0.1 |
| | | -- |
| | | |
| | | -- -------------------------------------------------------- |
| | |
| | | `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`) |
| | | ); |
| | | |
| | | -- -------------------------------------------------------- |
| | |
| | | `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`) |
| | | ); |
| | | |
| | |
| | | -- 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`); |
| | |
| | | -- RoundCube Webmail initial database structure |
| | | -- Version 0.1-rc1 |
| | | -- Version 0.1 |
| | | |
| | | -- -------------------------------------------------------- |
| | | |
| | |
| | | `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` |
| | |
| | | `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` |
| | |
| | | `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` |
| | |
| | | `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` |
| | |
| | | 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` |
| | |
| | | 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; |
| | |
| | | data text NOT NULL |
| | | ); |
| | | |
| | | |
| | | ALTER TABLE "cache" ADD INDEX (user_id, cache_key); |
| | | |
| | | -- |
| | | -- Sequence "message_ids" |
| | |
| | | -- 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); |
| | |
| | | -- RoundCube Webmail initial database structure |
| | | -- Version 0.1-rc1 |
| | | -- Version 0.1 |
| | | -- |
| | | |
| | | -- -------------------------------------------------------- |
| | |
| | | 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); |
| | | |
| | | |
| | | -- -------------------------------------------------------- |
| | |
| | | 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); |
| | |
| | | -- 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; |
| | | |
| | |
| | | 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); |
| | | |