- Rewritten messages caching (merged devel-mcache branch):

  Indexes are stored in a separate table, so there's no need to store all messages in a folder
  Added threads data caching
  Flags are stored separately, so flag change doesn't cause DELETE+INSERT, just UPDATE
- Partial QRESYNC support
- Improved FETCH response handling
- Improvements in response tokenization method

8 files changed, 463 insertions, 142 deletions

diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index 8e103eb55..eb5f20bcc 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -7,6 +7,37 @@ CREATE TABLE [dbo].[cache] (
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 GO

 

+CREATE TABLE [dbo].[cache_index] (

+	[user_id] [int] NOT NULL ,

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[changed] [datetime] NOT NULL ,

+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[cache_thread] (

+	[user_id] [int] NOT NULL ,

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[changed] [datetime] NOT NULL ,

+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[cache_messages] (

+	[user_id] [int] NOT NULL ,

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[uid] [int] NOT NULL ,

+	[changed] [datetime] NOT NULL ,

+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

+	[seen] [char](1) NOT NULL ,

+	[deleted] [char](1) NOT NULL ,

+	[answered] [char](1) NOT NULL ,

+	[forwarded] [char](1) NOT NULL ,

+	[flagged] [char](1) NOT NULL ,

+	[mdnsent] [char](1) NOT NULL ,

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

 CREATE TABLE [dbo].[contacts] (

 	[contact_id] [int] IDENTITY (1, 1) NOT NULL ,

 	[user_id] [int] NOT NULL ,

@@ -53,25 +84,6 @@ CREATE TABLE [dbo].[identities] (
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 GO

 

-CREATE TABLE [dbo].[messages] (

-	[message_id] [int] IDENTITY (1, 1) NOT NULL ,

-	[user_id] [int] NOT NULL ,

-	[del] [tinyint] NOT NULL ,

-	[cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

-	[created] [datetime] NOT NULL ,

-	[idx] [int] NOT NULL ,

-	[uid] [int] NOT NULL ,

-	[subject] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,

-	[from] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,

-	[to] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,

-	[cc] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,

-	[date] [datetime] NOT NULL ,

-	[size] [int] NOT NULL ,

-	[headers] [text] COLLATE Latin1_General_CI_AI NOT NULL ,

-	[structure] [text] COLLATE Latin1_General_CI_AI NULL 

-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-GO

-

 CREATE TABLE [dbo].[session] (

 	[sess_id] [varchar] (32) COLLATE Latin1_General_CI_AI NOT NULL ,

 	[created] [datetime] NOT NULL ,

@@ -116,6 +128,27 @@ ALTER TABLE [dbo].[cache] WITH NOCHECK ADD
 	)  ON [PRIMARY] 

 GO

 

+ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD 

+	 PRIMARY KEY CLUSTERED 

+	(

+		[user_id],[mailbox]

+	) ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD 

+	 PRIMARY KEY CLUSTERED 

+	(

+		[user_id],[mailbox]

+	) ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD 

+	 PRIMARY KEY CLUSTERED 

+	(

+		[user_id],[mailbox],[uid]

+	) ON [PRIMARY] 

+GO

+

 ALTER TABLE [dbo].[contacts] WITH NOCHECK ADD 

 	CONSTRAINT [PK_contacts_contact_id] PRIMARY KEY  CLUSTERED 

 	(

@@ -144,13 +177,6 @@ ALTER TABLE [dbo].[identities] WITH NOCHECK ADD
 	)  ON [PRIMARY] 

 GO

 

-ALTER TABLE [dbo].[messages] WITH NOCHECK ADD 

-	 PRIMARY KEY  CLUSTERED 

-	(

-		[message_id]

-	)  ON [PRIMARY] 

-GO

-

 ALTER TABLE [dbo].[session] WITH NOCHECK ADD 

 	CONSTRAINT [PK_session_sess_id] PRIMARY KEY  CLUSTERED 

 	(

@@ -187,6 +213,33 @@ GO
 CREATE  INDEX [IX_cache_created] ON [dbo].[cache]([created]) ON [PRIMARY]

 GO

 

+ALTER TABLE [dbo].[cache_index] ADD 

+	CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed]

+GO

+

+CREATE  INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[cache_thread] ADD 

+	CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed]

+GO

+

+CREATE  INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[cache_messages] ADD 

+	CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed]

+	CONSTRAINT [DF_cache_messages_seen] DEFAULT (0) FOR [seen],

+	CONSTRAINT [DF_cache_messages_deleted] DEFAULT (0) FOR [deleted],

+	CONSTRAINT [DF_cache_messages_answered] DEFAULT (0) FOR [answered],

+	CONSTRAINT [DF_cache_messages_forwarded] DEFAULT (0) FOR [forwarded],

+	CONSTRAINT [DF_cache_messages_flagged] DEFAULT (0) FOR [flagged],

+	CONSTRAINT [DF_cache_messages_mdnsent] DEFAULT (0) FOR [mdnsent],

+GO

+

+CREATE  INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]

+GO

+

 ALTER TABLE [dbo].[contacts] ADD 

 	CONSTRAINT [DF_contacts_user_id] DEFAULT (0) FOR [user_id],

 	CONSTRAINT [DF_contacts_changed] DEFAULT (getdate()) FOR [changed],

@@ -238,33 +291,6 @@ GO
 CREATE  INDEX [IX_identities_user_id] ON [dbo].[identities]([user_id]) ON [PRIMARY]

 GO

 

-ALTER TABLE [dbo].[messages] ADD 

-	CONSTRAINT [DF_messages_user_id] DEFAULT (0) FOR [user_id],

-	CONSTRAINT [DF_messages_del] DEFAULT (0) FOR [del],

-	CONSTRAINT [DF_messages_cache_key] DEFAULT ('') FOR [cache_key],

-	CONSTRAINT [DF_messages_created] DEFAULT (getdate()) FOR [created],

-	CONSTRAINT [DF_messages_idx] DEFAULT (0) FOR [idx],

-	CONSTRAINT [DF_messages_uid] DEFAULT (0) FOR [uid],

-	CONSTRAINT [DF_messages_subject] DEFAULT ('') FOR [subject],

-	CONSTRAINT [DF_messages_from] DEFAULT ('') FOR [from],

-	CONSTRAINT [DF_messages_to] DEFAULT ('') FOR [to],

-	CONSTRAINT [DF_messages_cc] DEFAULT ('') FOR [cc],

-	CONSTRAINT [DF_messages_date] DEFAULT (getdate()) FOR [date],

-	CONSTRAINT [DF_messages_size] DEFAULT (0) FOR [size]

-GO

-

-CREATE  INDEX [IX_messages_user_id] ON [dbo].[messages]([user_id]) ON [PRIMARY]

-GO

-

-CREATE  INDEX [IX_messages_cache_key] ON [dbo].[messages]([cache_key]) ON [PRIMARY]

-GO

-

-CREATE  INDEX [IX_messages_uid] ON [dbo].[messages]([uid]) ON [PRIMARY]

-GO

-

-CREATE  INDEX [IX_messages_created] ON [dbo].[messages]([created]) ON [PRIMARY]

-GO

-

 ALTER TABLE [dbo].[session] ADD 

 	CONSTRAINT [DF_session_sess_id] DEFAULT ('') FOR [sess_id],

 	CONSTRAINT [DF_session_created] DEFAULT (getdate()) FOR [created],

@@ -318,7 +344,17 @@ ALTER TABLE [dbo].[cache] ADD CONSTRAINT [FK_cache_user_id]
     ON DELETE CASCADE ON UPDATE CASCADE

 GO

 

-ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id]

+ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]

+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])

+    ON DELETE CASCADE ON UPDATE CASCADE

+GO

+

+ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]

+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])

+    ON DELETE CASCADE ON UPDATE CASCADE

+GO

+

+ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]

     FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])

     ON DELETE CASCADE ON UPDATE CASCADE

 GO

diff --git a/SQL/mssql.upgrade.sql b/SQL/mssql.upgrade.sql
index 258b1d78a..29440f950 100644
--- a/SQL/mssql.upgrade.sql
+++ b/SQL/mssql.upgrade.sql
@@ -151,3 +151,99 @@ ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id]
     ON DELETE CASCADE ON UPDATE CASCADE

 GO

 

+DROP TABLE [dbo].[messages]

+GO

+CREATE TABLE [dbo].[cache_index] (

+	[user_id] [int] NOT NULL ,

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[changed] [datetime] NOT NULL ,

+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[cache_thread] (

+	[user_id] [int] NOT NULL ,

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[changed] [datetime] NOT NULL ,

+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[cache_messages] (

+	[user_id] [int] NOT NULL ,

+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

+	[uid] [int] NOT NULL ,

+	[changed] [datetime] NOT NULL ,

+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

+	[seen] [char](1) NOT NULL ,

+	[deleted] [char](1) NOT NULL ,

+	[answered] [char](1) NOT NULL ,

+	[forwarded] [char](1) NOT NULL ,

+	[flagged] [char](1) NOT NULL ,

+	[mdnsent] [char](1) NOT NULL ,

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD 

+	 PRIMARY KEY CLUSTERED 

+	(

+		[user_id],[mailbox]

+	) ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD 

+	 PRIMARY KEY CLUSTERED 

+	(

+		[user_id],[mailbox]

+	) ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD 

+	 PRIMARY KEY CLUSTERED 

+	(

+		[user_id],[mailbox],[uid]

+	) ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[cache_index] ADD 

+	CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed]

+GO

+

+CREATE  INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[cache_thread] ADD 

+	CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed]

+GO

+

+CREATE  INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[cache_messages] ADD 

+	CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed]

+	CONSTRAINT [DF_cache_messages_seen] DEFAULT (0) FOR [seen],

+	CONSTRAINT [DF_cache_messages_deleted] DEFAULT (0) FOR [deleted],

+	CONSTRAINT [DF_cache_messages_answered] DEFAULT (0) FOR [answered],

+	CONSTRAINT [DF_cache_messages_forwarded] DEFAULT (0) FOR [forwarded],

+	CONSTRAINT [DF_cache_messages_flagged] DEFAULT (0) FOR [flagged],

+	CONSTRAINT [DF_cache_messages_mdnsent] DEFAULT (0) FOR [mdnsent],

+GO

+

+CREATE  INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]

+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])

+    ON DELETE CASCADE ON UPDATE CASCADE

+GO

+

+ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]

+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])

+    ON DELETE CASCADE ON UPDATE CASCADE

+GO

+

+ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]

+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])

+    ON DELETE CASCADE ON UPDATE CASCADE

+GO

+
\ No newline at end of file
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index a50e28c35..32b3991e0 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -33,33 +33,6 @@ CREATE TABLE `users` (
 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
 
--- Table structure for table `messages`
-
-CREATE TABLE `messages` (
- `message_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
- `del` tinyint(1) NOT NULL DEFAULT '0',
- `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
- `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
- `idx` int(11) UNSIGNED NOT NULL DEFAULT '0',
- `uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
- `subject` varchar(255) NOT NULL,
- `from` varchar(255) NOT NULL,
- `to` varchar(255) NOT NULL,
- `cc` varchar(255) NOT NULL,
- `date` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
- `size` int(11) UNSIGNED NOT NULL DEFAULT '0',
- `headers` text NOT NULL,
- `structure` text,
- PRIMARY KEY(`message_id`),
- CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
-   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `created_index` (`created`),
- INDEX `index_index` (`user_id`, `cache_key`, `idx`),
- UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-
 -- Table structure for table `cache`
 
 CREATE TABLE `cache` (
@@ -76,6 +49,55 @@ CREATE TABLE `cache` (
 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
 
+-- Table structure for table `cache_index`
+
+CREATE TABLE `cache_index` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+
+-- Table structure for table `cache_thread`
+
+CREATE TABLE `cache_thread` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+
+-- Table structure for table `cache_messages`
+
+CREATE TABLE `cache_messages` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ `seen` tinyint(1) NOT NULL DEFAULT '0',
+ `deleted` tinyint(1) NOT NULL DEFAULT '0',
+ `answered` tinyint(1) NOT NULL DEFAULT '0',
+ `forwarded` tinyint(1) NOT NULL DEFAULT '0',
+ `flagged` tinyint(1) NOT NULL DEFAULT '0',
+ `mdnsent` tinyint(1) NOT NULL DEFAULT '0',
+ CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`, `uid`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+
 -- Table structure for table `contacts`
 
 CREATE TABLE `contacts` (
diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql
index 7f8ce6154..fee18d640 100644
--- a/SQL/mysql.update.sql
+++ b/SQL/mysql.update.sql
@@ -170,3 +170,45 @@ CREATE TABLE `searches` (
     REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
   UNIQUE `uniqueness` (`user_id`, `type`, `name`)
 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+DROP TABLE `messages`;
+
+CREATE TABLE `cache_index` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `cache_thread` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `cache_messages` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ `seen` tinyint(1) NOT NULL DEFAULT '0',
+ `deleted` tinyint(1) NOT NULL DEFAULT '0',
+ `answered` tinyint(1) NOT NULL DEFAULT '0',
+ `forwarded` tinyint(1) NOT NULL DEFAULT '0',
+ `flagged` tinyint(1) NOT NULL DEFAULT '0',
+ `mdnsent` tinyint(1) NOT NULL DEFAULT '0',
+ CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`, `uid`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index 01221c4e3..a1864c6c9 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -67,7 +67,7 @@ CREATE SEQUENCE identity_ids
 CREATE TABLE identities (
     identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY,
     user_id integer NOT NULL
-	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
     changed timestamp with time zone DEFAULT now() NOT NULL,
     del smallint DEFAULT 0 NOT NULL,
     standard smallint DEFAULT 0 NOT NULL,
@@ -178,7 +178,7 @@ CREATE SEQUENCE cache_ids
 CREATE TABLE "cache" (
     cache_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY,
     user_id integer NOT NULL
-	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
     cache_key varchar(128) DEFAULT '' NOT NULL,
     created timestamp with time zone DEFAULT now() NOT NULL,
     data text NOT NULL
@@ -188,43 +188,59 @@ CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key);
 CREATE INDEX cache_created_idx ON "cache" (created);
 
 --
--- Sequence "message_ids"
--- Name: message_ids; Type: SEQUENCE; Schema: public; Owner: postgres
+-- Table "cache_index"
+-- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres
 --
 
-CREATE SEQUENCE message_ids
-    INCREMENT BY 1
-    NO MAXVALUE
-    NO MINVALUE
-    CACHE 1;
+CREATE TABLE cache_index (
+    user_id integer NOT NULL
+    	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    mailbox varchar(255) NOT NULL,
+    changed timestamp with time zone DEFAULT now() NOT NULL,
+    data text NOT NULL,
+    PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX cache_index_changed_idx ON cache_index (changed);
 
 --
--- Table "messages"
--- Name: messages; Type: TABLE; Schema: public; Owner: postgres
+-- Table "cache_thread"
+-- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres
 --
 
-CREATE TABLE messages (
-    message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY,
+CREATE TABLE cache_thread (
     user_id integer NOT NULL
-	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
-    del smallint DEFAULT 0 NOT NULL,
-    cache_key varchar(128) DEFAULT '' NOT NULL,
-    created timestamp with time zone DEFAULT now() NOT NULL,
-    idx integer DEFAULT 0 NOT NULL,
-    uid integer DEFAULT 0 NOT NULL,
-    subject varchar(128) DEFAULT '' NOT NULL,
-    "from" varchar(128) DEFAULT '' NOT NULL,
-    "to" varchar(128) DEFAULT '' NOT NULL,
-    cc varchar(128) DEFAULT '' NOT NULL,
-    date timestamp with time zone NOT NULL,
-    size integer DEFAULT 0 NOT NULL,
-    headers text NOT NULL,
-    structure text,
-    CONSTRAINT messages_user_id_key UNIQUE (user_id, cache_key, uid)
+    	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    mailbox varchar(255) NOT NULL,
+    changed timestamp with time zone DEFAULT now() NOT NULL,
+    data text NOT NULL,
+    PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX cache_thread_changed_idx ON cache_thread (changed);
+
+--
+-- Table "cache_messages"
+-- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres
+--
+
+CREATE TABLE cache_messages (
+    user_id integer NOT NULL
+    	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    mailbox varchar(255) NOT NULL,
+    uid integer NOT NULL,
+    changed timestamp with time zone DEFAULT now() NOT NULL,
+    data text NOT NULL,
+    seen smallint NOT NULL DEFAULT 0,
+    deleted smallint NOT NULL DEFAULT 0,
+    answered smallint NOT NULL DEFAULT 0,
+    forwarded smallint NOT NULL DEFAULT 0,
+    flagged smallint NOT NULL DEFAULT 0,
+    mdnsent smallint NOT NULL DEFAULT 0,
+    PRIMARY KEY (user_id, mailbox, uid)
 );
 
-CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
-CREATE INDEX messages_created_idx ON messages (created);
+CREATE INDEX cache_messages_changed_idx ON cache_messages (changed);
 
 --
 -- Table "dictionary"
diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql
index e316ff540..4bd0400c9 100644
--- a/SQL/postgres.update.sql
+++ b/SQL/postgres.update.sql
@@ -126,3 +126,46 @@ CREATE TABLE searches (
     data text NOT NULL,
     CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name)
 );
+
+DROP SEQUENCE messages_ids;
+DROP TABLE messages;
+
+CREATE TABLE cache_index (
+    user_id integer NOT NULL
+    	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    mailbox varchar(255) NOT NULL,
+    changed timestamp with time zone DEFAULT now() NOT NULL,
+    data text NOT NULL,
+    PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX cache_index_changed_idx ON cache_index (changed);
+
+CREATE TABLE cache_thread (
+    user_id integer NOT NULL
+    	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    mailbox varchar(255) NOT NULL,
+    changed timestamp with time zone DEFAULT now() NOT NULL,
+    data text NOT NULL,
+    PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX cache_thread_changed_idx ON cache_thread (changed);
+
+CREATE TABLE cache_messages (
+    user_id integer NOT NULL
+    	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    mailbox varchar(255) NOT NULL,
+    uid integer NOT NULL,
+    changed timestamp with time zone DEFAULT now() NOT NULL,
+    data text NOT NULL,
+    seen smallint NOT NULL DEFAULT 0,
+    deleted smallint NOT NULL DEFAULT 0,
+    answered smallint NOT NULL DEFAULT 0,
+    forwarded smallint NOT NULL DEFAULT 0,
+    flagged smallint NOT NULL DEFAULT 0,
+    mdnsent smallint NOT NULL DEFAULT 0,
+    PRIMARY KEY (user_id, mailbox, uid)
+);
+
+CREATE INDEX cache_messages_changed_idx ON cache_messages (changed);
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index 46ee5301b..7ec82015d 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -1,7 +1,7 @@
 -- Roundcube Webmail initial database structure
 
 -- 
--- Table structure for table `cache`
+-- Table structure for table cache
 -- 
 
 CREATE TABLE cache (
@@ -9,7 +9,7 @@ CREATE TABLE cache (
   user_id integer NOT NULL default 0,
   cache_key varchar(128) NOT NULL default '',
   created datetime NOT NULL default '0000-00-00 00:00:00',
-  data longtext NOT NULL
+  data text NOT NULL
 );
 
 CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
@@ -121,34 +121,6 @@ CREATE INDEX ix_session_changed ON session (changed);
 
 -- --------------------------------------------------------
 
--- 
--- Table structure for table messages
--- 
-
-CREATE TABLE messages (
-  message_id integer NOT NULL PRIMARY KEY,
-  user_id integer NOT NULL default '0',
-  del tinyint NOT NULL default '0',
-  cache_key varchar(128) NOT NULL default '',
-  created datetime NOT NULL default '0000-00-00 00:00:00',
-  idx integer NOT NULL default '0',
-  uid integer 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 integer NOT NULL default '0',
-  headers text NOT NULL,
-  structure text
-);
-
-CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid);
-CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx);
-CREATE INDEX ix_messages_created ON messages (created);
-
--- --------------------------------------------------------
-
 --
 -- Table structure for table dictionary
 --
@@ -176,3 +148,58 @@ CREATE TABLE searches (
 );
 
 CREATE UNIQUE INDEX ix_searches_user_type_name (user_id, type, name);
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table cache_index
+--
+
+CREATE TABLE cache_index (
+    user_id integer NOT NULL,
+    mailbox varchar(255) NOT NULL,
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    data text NOT NULL,
+    PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX ix_cache_index_changed ON cache_index (changed);
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table cache_thread
+--
+
+CREATE TABLE cache_thread (
+    user_id integer NOT NULL,
+    mailbox varchar(255) NOT NULL,
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    data text NOT NULL,
+    PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX ix_cache_thread_changed ON cache_thread (changed);
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table cache_messages
+--
+
+CREATE TABLE cache_messages (
+    user_id integer NOT NULL,
+    mailbox varchar(255) NOT NULL,
+    uid integer NOT NULL,
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    data text NOT NULL,
+    seen smallint NOT NULL DEFAULT '0',
+    deleted smallint NOT NULL DEFAULT '0',
+    answered smallint NOT NULL DEFAULT '0',
+    forwarded smallint NOT NULL DEFAULT '0',
+    flagged smallint NOT NULL DEFAULT '0',
+    mdnsent smallint NOT NULL DEFAULT '0',
+    PRIMARY KEY (user_id, mailbox, uid)
+);
+
+CREATE INDEX ix_cache_messages_changed ON cache_messages (changed);
diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql
index 41ab0200d..378072c03 100644
--- a/SQL/sqlite.update.sql
+++ b/SQL/sqlite.update.sql
@@ -223,11 +223,11 @@ INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname,
 CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
 DROP TABLE contacts_tmp;
 
+
 DELETE FROM messages;
 DELETE FROM cache;
 CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
 
-
 -- Updates from version 0.6-stable
 
 CREATE TABLE dictionary (
@@ -247,3 +247,42 @@ CREATE TABLE searches (
 );
 
 CREATE UNIQUE INDEX ix_searches_user_type_name (user_id, type, name);
+
+DROP TABLE messages;
+
+CREATE TABLE cache_index (
+    user_id integer NOT NULL,
+    mailbox varchar(255) NOT NULL,
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    data text NOT NULL,
+    PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX ix_cache_index_changed ON cache_index (changed);
+
+CREATE TABLE cache_thread (
+    user_id integer NOT NULL,
+    mailbox varchar(255) NOT NULL,
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    data text NOT NULL,
+    PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX ix_cache_thread_changed ON cache_thread (changed);
+
+CREATE TABLE cache_messages (
+    user_id integer NOT NULL,
+    mailbox varchar(255) NOT NULL,
+    uid integer NOT NULL,
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    data text NOT NULL,
+    seen smallint NOT NULL DEFAULT '0',
+    deleted smallint NOT NULL DEFAULT '0',
+    answered smallint NOT NULL DEFAULT '0',
+    forwarded smallint NOT NULL DEFAULT '0',
+    flagged smallint NOT NULL DEFAULT '0',
+    mdnsent smallint NOT NULL DEFAULT '0',
+    PRIMARY KEY (user_id, mailbox, uid)
+);
+
+CREATE INDEX ix_cache_messages_changed ON cache_messages (changed);