[JDEV] xdb_sql suggestions

[Raditha Dissanayake]

Hello Matthias

You are hardly the person we expect to ask questions on this list :-)

I guess Bentoit and Co might have left the tables without indices 
because there's a large number of inserts being done on these tables.  
That's because mod_roster passes the entire roster to XDB when a change 
occurs. xdb_sql deletes the whole lot and inserts them anew.
I did a patch for this sometime back but never got around to 
contributing it. With that patch the roster entries just get updated 
instead of delete/insert.   Let me try to dig it up and send it in.

best regards
raditha





Matthias Wimmer wrote:

>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
>  
>


-- 
http://www.raditha.com/php/progress.php
A progress bar for PHP file uploads.