[JDEV] xdb_sql suggestions

[Matthias Wimmer]

Hi!

I have some suggestions for the database schema of xdb_sql.

- At least in the MySQL version the table rostergroups has no index.
  Therefore a query has to read the complete table. Adding a index on
  username and jid improves the query speed very much.

  CREATE INDEX rostergrpuserjid ON rostergroups (username(32), jid(32);

- The table rosterusers uses char(1) fields for enums. I would redefine
  it as:

  CREATE TABLE rosterusers (
    username VARCHAR(64) NOT NULL,
    jid VARCHAR(64) NOT NULL,
    nick VARCHAR(255),
    subscription ENUM('N', 'T', 'F', 'B') NOT NULL,
    ask ENUM('-', 'S', 'U') NOT NULL,
    server ENUM('Y', 'N') NOT NULL,
    subscribe VARCHAR(255),
    type VARCHAR(64),
    INDEX rostuser (username(32))
  );



Tot kijk
   Matthias
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
URL: <https://www.jabber.org/jdev/attachments/20030905/249eb509/attachment-0002.pgp>