From 23b76563dcc2542cff9f447e809462472cfccd88 Mon Sep 17 00:00:00 2001 From: alecpl Date: Tue, 29 Sep 2009 07:59:25 +0000 Subject: - use better index for contacts table --- SQL/mysql.initial.sql | 1 + SQL/mysql.update.sql | 3 +++ SQL/postgres.initial.sql | 2 +- SQL/postgres.update.sql | 2 ++ SQL/sqlite.initial.sql | 2 +- SQL/sqlite.update.sql | 2 ++ 6 files changed, 10 insertions(+), 2 deletions(-) diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 1bc888458..f8145471f 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -95,6 +95,7 @@ CREATE TABLE `contacts` ( `vcard` text NULL, `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY(`contact_id`), + INDEX `user_contacts_index` (`user_id`,`email`), CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) /*!40008 diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql index 729fd3c66..77bf317e9 100644 --- a/SQL/mysql.update.sql +++ b/SQL/mysql.update.sql @@ -54,3 +54,6 @@ TRUNCATE `messages`; ALTER TABLE `session` CHANGE `vars` `vars` MEDIUMTEXT NOT NULL; + +ALTER TABLE `contacts` + ADD INDEX `user_contacts_index` (`user_id`,`email`); diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index f07ca3029..a52a01f91 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -110,7 +110,7 @@ CREATE TABLE contacts ( vcard text ); -CREATE INDEX contacts_user_id_idx ON contacts (user_id); +CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); -- -- Sequence "cache_ids" diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql index fe88a6dcc..e343dd440 100644 --- a/SQL/postgres.update.sql +++ b/SQL/postgres.update.sql @@ -41,3 +41,5 @@ ALTER TABLE messages ALTER del TYPE smallint; CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx); TRUNCATE messages; +DROP INDEX contacts_user_id_idx; +CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index 2395cfd49..cccad7a20 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -34,7 +34,7 @@ CREATE TABLE contacts ( vcard text NOT NULL default '' ); -CREATE INDEX ix_contacts_user_id ON contacts(user_id); +CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); -- -------------------------------------------------------- diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql index 4b1b10b7f..09af76fae 100644 --- a/SQL/sqlite.update.sql +++ b/SQL/sqlite.update.sql @@ -41,3 +41,5 @@ DROP INDEX ix_messages_user_cache_uid; 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); TRUNCATE messages; +DROP INDEX ix_contacts_user_id; +CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); -- cgit v1.2.3