[JDEV] xdb_sql suggestions

[Raditha Dissanayake]

Hi Matthias,

You could be right about the index being forgotten :-) and you are right 
about making an index with a smaller width than the full column. But i 
haven't seen such a drastic drop in performance as you seem to have 
encountered without indices.

One other point that i noted is that most of the key fields are not 
numeric this also leads to a big drop in performance both for inserts 
and retrievals.

best regards
raditha


Matthias Wimmer wrote:

>Hi Raditha!
>
>Raditha Dissanayake schrieb am 2003-09-05 19:20:00:
>  
>
>>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.
>>    
>>
>
>I don't know if it is faster without an index on small databases, but on
>amessage the difference is enormous. Without the index it was not really
>usable. It the insert operation would get that expensive with the
>indexes, I don't think it would exist on the rosterusers table. It think
>the index has just been forgotten.
>
>And on the rosterusers table the index could get smaller and wouldn't
>decrease performance if it is limited to maybe the first 32 characters
>of username and jid.
>
>
>Tot kijk
>    Matthias
>  
>


-- 
http://www.radinks.com/upload
Drag and Drop File Uploader.