[Devel] [ openser-Bugs-1675964 ] Presence SQL table needs index on to_tag or revised query

[SourceForge.net]

Bugs item #1675964, was opened at 2007-03-07 18:11
Message generated for change (Comment added) made by amr42
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=743020&aid=1675964&group_id=139143

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: modules
Group: ver devel
>Status: Open
Resolution: Fixed
Priority: 5
Private: No
Submitted By: Aron Rosenberg (amr42)
Assigned to: anca (anca_vamanu)
Summary: Presence SQL table needs index on to_tag or revised query

Initial Comment:
In the new presence module there is a general query that runs without an index.

At notify.c:1578, a 'update active_watchers set CSEQ=n status='nn' where to_tag=blah' is done.

The to_tag column has no index in the current SQL schema which causes a full table scan and a very slow query.

There are two solutions:

1. Add an index on the to_tag column.

2. Change the query so that it uses the from_tag instead of the to_tag. This will cause it to use the index already created on from_tag (UNIQUE)


If possible (2) is the better choice since it reduces the number of indexes needed on that table.

----------------------------------------------------------------------

>Comment By: Aron Rosenberg (amr42)
Date: 2007-03-13 16:47

Message:
Logged In: YES 
user_id=43318
Originator: YES

Anca,

The index change to to_tag now leaves a bunch of queries on from_tag that
have no index. The better fix would be to change the one query at
notify.c:1578 to search on from_tag instead of to_tag so that the number of
indexes is minimized.

Becuase this change results in worse performance I am reopening the bug.

----------------------------------------------------------------------

Comment By: anca (anca_vamanu)
Date: 2007-03-08 17:22

Message:
Logged In: YES 
user_id=1614776
Originator: NO

Hello,

Thanks for reporting.
The uniques index has been changed to to_tag.

Best regards, 

Anca Vamanu

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=743020&aid=1675964&group_id=139143