[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 (Settings changed) 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: None
Priority: 5
Private: No
Submitted By: Aron Rosenberg (amr42)
Assigned to: Nobody/Anonymous (nobody)
>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.

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

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