Add subj_uri_id and subj_origin_id columns to event_view

Bug #642686 reported by Markus Korn on 2010-09-19
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Zeitgeist Framework
Fix Released
Wishlist
Markus Korn

Bug Description

If we add subj_uri_id and subj_origin_id columns to the event_view we could make the prefix searches about 10% faster. Combined with the upcoming optimization of the GLOB statement it will be possible to reduce the avg. time for this kind of queries from ~0.20s to ~0.12s.

As always I've added a script where you can test the proposed change to event_view yourself.

*** 551 entries starting with 'http://aa'

SELECT DISTINCT id FROM event_view WHERE (timestamp >= ? AND timestamp <= ? AND ((subj_uri IN (SELECT value FROM uri WHERE value GLOB ?)))) ORDER BY timestamp DESC [u'0', u'9223372036854775807', u'http://aa*']
---> get len(ids)=551 using pure sql in 0.206047s

SELECT DISTINCT id FROM event_view_new WHERE (timestamp >= ? AND timestamp <= ? AND ((subj_uri_id IN (SELECT id FROM uri WHERE value GLOB ?)))) ORDER BY timestamp DESC [u'0', u'9223372036854775807', u'http://aa*']
---> get len(ids)=551 using pure sql in 0.179763s

Related branches

Markus Korn (thekorn) wrote :
Seif Lotfy (seif) on 2010-09-19
Changed in zeitgeist:
importance: Undecided → Wishlist
assignee: nobody → Markus Korn (thekorn)
assignee: Markus Korn (thekorn) → nobody
Markus Korn (thekorn) on 2010-09-19
Changed in zeitgeist:
assignee: nobody → Markus Korn (thekorn)
status: New → In Progress
Markus Korn (thekorn) on 2010-09-19
Changed in zeitgeist:
status: In Progress → Fix Committed
Changed in zeitgeist:
milestone: none → 0.6
Markus Korn (thekorn) on 2010-11-01
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