[Devel] [ openser-Bugs-1675964 ] Presence SQL table needs index on
to_tag or revised query
SourceForge.net
noreply at sourceforge.net
Fri Mar 16 17:43:30 CET 2007
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: Closed
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-16 16:43
Message:
Logged In: YES
user_id=43318
Originator: YES
Anca,
With the latest SVN, you are correct. I will close this bug again.
----------------------------------------------------------------------
Comment By: anca (anca_vamanu)
Date: 2007-03-16 12:31
Message:
Logged In: YES
user_id=1614776
Originator: NO
Hello,
I have analized the code and found no query that uses only "from_tag",
there is on that uses both from_tag and to_tag, and two that run only on
to_tag.
I don't see how the change could result in worse performance.
If I am missing something please point it out.
Best regards,
Anca Vamanu
----------------------------------------------------------------------
Comment By: anca (anca_vamanu)
Date: 2007-03-16 12:07
Message:
Logged In: YES
user_id=1614776
Originator: NO
Hello,
I have analised the code. I have not found any query that uses only
"from_tag",
there is one that uses both from_tag and to_tag and 2 which use only
to_tag.
I see no reason why this change could result in worse performance.
If I am missing something out please point it out.
Best regards,
Anca Vamanu
----------------------------------------------------------------------
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
More information about the Devel
mailing list