[Zeitgeist] [Bug 673452] Re: Using the subj_origin column of event_view is slower than it should be
** Changed in: zeitgeist Status: In Progress = Fix Committed -- Using the subj_origin column of event_view is slower than it should be https://bugs.launchpad.net/bugs/673452 You received this bug notification because you are a member of Zeitgeist Framework Team, which is subscribed to Zeitgeist Framework. Status in Zeitgeist Framework: Fix Committed Bug description: Let me summarize what we have wrt subj_origin 1) we have a subj_origin column in the event table, which is an integer pointing to an entry in the uri table 2) we have an index on the subj_origin column of the event table 3) we have subj_origin in event view, which is not the id, but the resolved uri 4) we have subj_origin_id in event_view which is in fact the same as event.subj_uri, but we get this value by using a sub-query on the uri table. Points 3) and 4) are the reason why the index on event.subj_origin is never used when querying the event_view, example: DEBUG:zeitgeist.sql:Got query: QUERY: SELECT id FROM event_view WHERE subj_origin_id=1 ([]) PLAN: [0, 0, u'TABLE event'] [0, 0, u'TABLE uri USING PRIMARY KEY'] By simply renaming event_view.subj_origin to event_view.subj_origin_uri and event_view.subj_origin_id to event_view.subj_origin in combination with removing the sub-query we can make queries for the origin id on event_view *much* faster. ___ Mailing list: https://launchpad.net/~zeitgeist Post to : zeitgeist@lists.launchpad.net Unsubscribe : https://launchpad.net/~zeitgeist More help : https://help.launchpad.net/ListHelp
[Zeitgeist] [Bug 673452] Re: Using the subj_origin column of event_view is slower than it should be
** Branch linked: lp:~thekorn/zeitgeist/db_schema_3 ** Branch unlinked: lp:~thekorn/zeitgeist/faster_origin_queries ** Changed in: zeitgeist Assignee: (unassigned) = Markus Korn (thekorn) ** Changed in: zeitgeist Status: Triaged = In Progress -- Using the subj_origin column of event_view is slower than it should be https://bugs.launchpad.net/bugs/673452 You received this bug notification because you are a member of Zeitgeist Framework Team, which is subscribed to Zeitgeist Framework. Status in Zeitgeist Framework: In Progress Bug description: Let me summarize what we have wrt subj_origin 1) we have a subj_origin column in the event table, which is an integer pointing to an entry in the uri table 2) we have an index on the subj_origin column of the event table 3) we have subj_origin in event view, which is not the id, but the resolved uri 4) we have subj_origin_id in event_view which is in fact the same as event.subj_uri, but we get this value by using a sub-query on the uri table. Points 3) and 4) are the reason why the index on event.subj_origin is never used when querying the event_view, example: DEBUG:zeitgeist.sql:Got query: QUERY: SELECT id FROM event_view WHERE subj_origin_id=1 ([]) PLAN: [0, 0, u'TABLE event'] [0, 0, u'TABLE uri USING PRIMARY KEY'] By simply renaming event_view.subj_origin to event_view.subj_origin_uri and event_view.subj_origin_id to event_view.subj_origin in combination with removing the sub-query we can make queries for the origin id on event_view *much* faster. ___ Mailing list: https://launchpad.net/~zeitgeist Post to : zeitgeist@lists.launchpad.net Unsubscribe : https://launchpad.net/~zeitgeist More help : https://help.launchpad.net/ListHelp
[Zeitgeist] [Bug 673452] Re: Using the subj_origin column of event_view is slower than it should be
** Branch linked: lp:~thekorn/zeitgeist/faster_origin_queries -- Using the subj_origin column of event_view is slower than it should be https://bugs.launchpad.net/bugs/673452 You received this bug notification because you are a member of Zeitgeist Framework Team, which is subscribed to Zeitgeist Framework. Status in Zeitgeist Framework: New Bug description: Let me summarize what we have wrt subj_origin 1) we have a subj_origin column in the event table, which is an integer pointing to an entry in the uri table 2) we have an index on the subj_origin column of the event table 3) we have subj_origin in event view, which is not the id, but the resolved uri 4) we have subj_origin_id in event_view which is in fact the same as event.subj_uri, but we get this value by using a sub-query on the uri table. Points 3) and 4) are the reason why the index on event.subj_origin is never used when querying the event_view, example: DEBUG:zeitgeist.sql:Got query: QUERY: SELECT id FROM event_view WHERE subj_origin_id=1 ([]) PLAN: [0, 0, u'TABLE event'] [0, 0, u'TABLE uri USING PRIMARY KEY'] By simply renaming event_view.subj_origin to event_view.subj_origin_uri and event_view.subj_origin_id to event_view.subj_origin in combination with removing the sub-query we can make queries for the origin id on event_view *much* faster. ___ Mailing list: https://launchpad.net/~zeitgeist Post to : zeitgeist@lists.launchpad.net Unsubscribe : https://launchpad.net/~zeitgeist More help : https://help.launchpad.net/ListHelp
[Zeitgeist] [Bug 673452] Re: Using the subj_origin column of event_view is slower than it should be
** Changed in: zeitgeist Status: New = Triaged -- Using the subj_origin column of event_view is slower than it should be https://bugs.launchpad.net/bugs/673452 You received this bug notification because you are a member of Zeitgeist Framework Team, which is subscribed to Zeitgeist Framework. Status in Zeitgeist Framework: Triaged Bug description: Let me summarize what we have wrt subj_origin 1) we have a subj_origin column in the event table, which is an integer pointing to an entry in the uri table 2) we have an index on the subj_origin column of the event table 3) we have subj_origin in event view, which is not the id, but the resolved uri 4) we have subj_origin_id in event_view which is in fact the same as event.subj_uri, but we get this value by using a sub-query on the uri table. Points 3) and 4) are the reason why the index on event.subj_origin is never used when querying the event_view, example: DEBUG:zeitgeist.sql:Got query: QUERY: SELECT id FROM event_view WHERE subj_origin_id=1 ([]) PLAN: [0, 0, u'TABLE event'] [0, 0, u'TABLE uri USING PRIMARY KEY'] By simply renaming event_view.subj_origin to event_view.subj_origin_uri and event_view.subj_origin_id to event_view.subj_origin in combination with removing the sub-query we can make queries for the origin id on event_view *much* faster. ___ Mailing list: https://launchpad.net/~zeitgeist Post to : zeitgeist@lists.launchpad.net Unsubscribe : https://launchpad.net/~zeitgeist More help : https://help.launchpad.net/ListHelp
