Changeset 1218 in subversion


Ignore:
Timestamp:
Mar 25, 2008 3:08:19 PM (5 years ago)
Author:
thomasb
Message:

Optimize database schema; get rid of unnecessary indexes

Location:
trunk/roundcubemail/SQL
Files:
1 deleted
7 edited

Legend:

Unmodified
Added
Removed

  • trunk/roundcubemail/SQL/mysql.initial.sql

    r861 r1218  
    11-- RoundCube Webmail initial database structure 
    2 -- Version 0.1-rc1 
     2-- Version 0.1 
    33--  
    44 
    … …  
    1717  `data` longtext NOT NULL, 
    1818  PRIMARY KEY  (`cache_id`), 
    19   KEY `user_id` (`user_id`), 
    20   KEY `cache_key` (`cache_key`), 
    21   KEY `session_id` (`session_id`) 
     19  INDEX `user_cache_index` (`user_id`,`cache_key`) 
    2220); 
    2321 
    … …  
    119117  `headers` text NOT NULL, 
    120118  `structure` text, 
    121   PRIMARY KEY  (`message_id`), 
    122   KEY `user_id` (`user_id`), 
    123   KEY `idx` (`idx`), 
    124   KEY `uid` (`uid`), 
     119  PRIMARY KEY (`message_id`), 
    125120  UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`) 
    126121); 

  • trunk/roundcubemail/SQL/mysql.update.sql

    r803 r1218  
    11-- RoundCube Webmail update script for MySQL databases 
    2 -- Updates from version 0.1-beta and 0.1-beta2 
     2-- Updates from version 0.1-stable to 0.1.1 
    33 
    44TRUNCATE TABLE `messages`; 
    55 
    66ALTER TABLE `messages` 
    7   DROP `body`, 
     7  DROP INDEX `idx`, 
     8  DROP INDEX `uid`; 
     9 
     10ALTER TABLE `cache` 
    811  DROP INDEX `cache_key`, 
    9   ADD `structure` TEXT, 
    10   ADD UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`); 
    11  
    12 ALTER TABLE `identities` 
    13   ADD `html_signature` tinyint(1) default 0 NOT NULL; 
    14  
    15 ALTER TABLE `session` CHANGE `ip` `ip` VARCHAR(40)  
    16    
    17 -- Uncomment these lines if you're using MySQL 4.1 or higher 
    18 -- ALTER TABLE `users` 
    19 --  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci, 
    20 --  CHANGE `username` `username` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 
    21 --  CHANGE `alias` `alias` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL; 
     12  DROP INDEX `session_id`, 
     13  ADD INDEX `user_cache_index` (`user_id`,`cache_key`); 

  • trunk/roundcubemail/SQL/mysql5.initial.sql

    r861 r1218  
    11-- RoundCube Webmail initial database structure 
    2 -- Version 0.1-rc1 
     2-- Version 0.1 
    33 
    44-- -------------------------------------------------------- 
    … …  
    1616 `vars` text NOT NULL, 
    1717 PRIMARY KEY(`sess_id`) 
    18 ) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; 
     18) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; 
    1919 
    2020 
    … …  
    3131 `preferences` text, 
    3232 PRIMARY KEY(`user_id`) 
    33 ) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; 
     33) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; 
    3434 
    3535 
    … …  
    5353 `structure` text, 
    5454 PRIMARY KEY(`message_id`), 
    55  INDEX `idx`(`idx`), 
    56  INDEX `uid`(`uid`), 
    5755 UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`), 
    5856 CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) 
    … …  
    6058     ON DELETE CASCADE 
    6159     ON UPDATE CASCADE 
    62 ) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; 
     60) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; 
    6361 
    6462 
    … …  
    7371 `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', 
    7472 PRIMARY KEY(`cache_id`), 
    75  INDEX `cache_key`(`cache_key`), 
    76  INDEX `session_id`(`session_id`), 
     73 INDEX `user_cache_index` (`user_id`,`cache_key`), 
    7774 CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) 
    7875   REFERENCES `users`(`user_id`) 
    7976     ON DELETE CASCADE 
    8077     ON UPDATE CASCADE 
    81 ) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; 
     78) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; 
    8279 
    8380 
    … …  
    9996     ON DELETE CASCADE 
    10097     ON UPDATE CASCADE 
    101 ) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; 
     98) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; 
    10299 
    103100 
    … …  
    121118     ON DELETE CASCADE 
    122119     ON UPDATE CASCADE 
    123 ) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; 
     120) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; 
    124121 
    125122 

  • trunk/roundcubemail/SQL/postgres.initial.sql

    r803 r1218  
    132132); 
    133133 
    134  
     134ALTER TABLE "cache" ADD INDEX (user_id, cache_key); 
    135135 
    136136-- 

  • trunk/roundcubemail/SQL/postgres.update.sql

    r923 r1218  
    1 -- RoundCube Webmail update script for Postres databases 
    2 -- Updates from version 0.1-beta and older 
     1-- RoundCube Webmail update script for Postgres databases 
     2-- Updates from version 0.1-stable to 0.1.1 
    33 
    4 ALTER TABLE "messages" DROP body; 
    5 ALTER TABLE "messages" ADD structure TEXT; 
    6 ALTER TABLE "messages" ADD UNIQUE (user_id, cache_key, uid); 
    7  
    8 ALTER TABLE "identities" ADD html_signature INTEGER; 
    9 ALTER TABLE "identities" ALTER html_signature SET DEFAULT 0; 
    10 UPDATE identities SET html_signature = 0; 
    11 ALTER TABLE "identities" ALTER html_signature SET NOT NULL; 
    12  
     4ALTER TABLE "cache" ADD INDEX (user_id, cache_key); 

  • trunk/roundcubemail/SQL/sqlite.initial.sql

    r803 r1218  
    11-- RoundCube Webmail initial database structure 
    2 -- Version 0.1-rc1 
     2-- Version 0.1 
    33--  
    44 
    … …  
    1818); 
    1919 
    20 CREATE INDEX ix_cache_user_id ON cache(user_id); 
    21 CREATE INDEX ix_cache_cache_key ON cache(cache_key); 
    22 CREATE INDEX ix_cache_session_id ON cache(session_id); 
     20CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key); 
    2321 
    2422 
    … …  
    116114  "from" varchar(255) NOT NULL default '', 
    117115  "to" varchar(255) NOT NULL default '', 
    118   cc varchar(255) NOT NULL default '', 
    119   date datetime NOT NULL default '0000-00-00 00:00:00', 
     116  "cc" varchar(255) NOT NULL default '', 
     117  "date" datetime NOT NULL default '0000-00-00 00:00:00', 
    120118  size integer NOT NULL default '0', 
    121119  headers text NOT NULL, 
    … …  
    123121); 
    124122 
    125 CREATE INDEX ix_messages_user_id ON messages(user_id); 
    126 CREATE INDEX ix_messages_cache_key ON messages(cache_key); 
    127 CREATE INDEX ix_messages_idx ON messages(idx); 
    128 CREATE INDEX ix_messages_uid ON messages(uid); 
     123CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid); 

  • trunk/roundcubemail/SQL/sqlite.update.sql

    r325 r1218  
    11-- RoundCube Webmail update script for SQLite databases 
    2 -- Updates from version 0.1-beta2 and older 
     2-- Updates from version 0.1-stable to 0.1.1 
    33 
    44DROP TABLE messages; 
    … …  
    1515  "from" varchar(255) NOT NULL default '', 
    1616  "to" varchar(255) NOT NULL default '', 
    17   cc varchar(255) NOT NULL default '', 
    18   date datetime NOT NULL default '0000-00-00 00:00:00', 
     17  "cc" varchar(255) NOT NULL default '', 
     18  "date" datetime NOT NULL default '0000-00-00 00:00:00', 
    1919  size integer NOT NULL default '0', 
    2020  headers text NOT NULL, 
    … …  
    2222); 
    2323 
    24 CREATE INDEX ix_messages_user_id ON messages(user_id); 
    25 CREATE INDEX ix_messages_cache_key ON messages(cache_key); 
    26 CREATE INDEX ix_messages_idx ON messages(idx); 
    27 CREATE INDEX ix_messages_uid ON messages(uid); 
     24CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid); 
     25 
Note: See TracChangeset for help on using the changeset viewer.