Using the subj_origin column of event_view is slower than it should be

Bug #673452 reported by Markus Korn on 2010-11-10
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Zeitgeist Framework
Fix Released
Undecided
Markus Korn

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.

Related branches

Seif Lotfy (seif) on 2010-11-10
Changed in zeitgeist:
status: New → Triaged
Markus Korn (thekorn) on 2010-11-12
Changed in zeitgeist:
assignee: nobody → Markus Korn (thekorn)
status: Triaged → In Progress
Seif Lotfy (seif) on 2010-11-16
Changed in zeitgeist:
milestone: none → 0.7.0
Markus Korn (thekorn) on 2010-11-16
Changed in zeitgeist:
status: In Progress → Fix Committed
Seif Lotfy (seif) on 2011-01-18
Changed in zeitgeist:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers