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 _______________________________________________ Devel mailing list [email protected] http://openser.org/cgi-bin/mailman/listinfo/devel
