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 _______________________________________________ Mailing list: https://launchpad.net/~zeitgeist Post to : zeitgeist@lists.launchpad.net Unsubscribe : https://launchpad.net/~zeitgeist More help : https://help.launchpad.net/ListHelp
