Opened 5 years ago

Closed 5 years ago

#1484854 closed Feature Requests (fixed)

no indices in postgres database schema bug + DDL improvements

Reported by: alec Owned by:
Priority: 5 Milestone: 0.1.1
Component: Database Version: 0.1-stable
Severity: normal Keywords:
Cc:

Description

  1. postgres is not creating indices for "REFERENCES" columns, so we must to create them explicitly:

CREATE INDEX contacts_user_id_idx ON contacts (user_id);
CREATE INDEX identities_user_id_idx ON identities (user_id);
CREATE INDEX cache_user_id_idx ON cache (user_id);
CREATE INDEX cache_session_id_idx ON cache (session_id);

  1. also I think that few indices defined in mysql ddl script aren't used at all and can be removed, i.e. messages/uid, message/idx, also cache/cache_key
  1. Proposition for new indices:

CREATE INDEX users_username_id_idx ON users (username);
CREATE INDEX users_alias_id_idx ON users (alias);
they are used by query in function rcube_user::query()

Change History (4)

comment:1 Changed 5 years ago by alec

Also ON DELETE CASCADE should be used, because when I delete user (remove account from mail server), all referenced data should be deleted automatically.

comment:2 Changed 5 years ago by till

  • Type changed from Bugs to Feature Requests

Can you supply a patch for your requests?

comment:3 Changed 5 years ago by thomasb

  1. is fixed in [ac26b964]

comment:4 Changed 5 years ago by alec

  • Resolution set to fixed
  • Status changed from new to closed

Fixed in [6cb77869]

Note: See TracTickets for help on using tickets.