zeitgeist team mailing list archive

[Markus Korn <thekorn@xxxxxx>]

Public bug reported:

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

** Affects: zeitgeist
     Importance: Wishlist
         Status: New

-- 
Add subj_uri_id and subj_origin_id columns to event_view
https://bugs.launchpad.net/bugs/642686
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:
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