I have attempted to reformat your query to make it more readable. Did I translate it correctly?
CREATE TABLE infos( id INTEGER PRIMARY KEY, name TEXT, text TEXT, zone INTEGER DEFAULT 3, dateAdded DATATIME, lastModified DATETIME, votes INTEGER DEFAULT 0 NOT NULL, rating INTEGER DEFAULT 0 NOT NULL, expiration DATETIME, exp_action INTEGER, date DATETIME ); CREATE TABLE category( id INTEGER PRIMARY KEY, name TEXT UNIQUE, parent INTEGER, zone INTEGER ); CREATE TABLE category_item( id INTEGER PRIMARY KEY, category_id INTEGER NOT NULL, item_id INTEGER NOT NULL, item_type_id INTEGER NOT NULL, UNIQUE(category_id,item_id,item_type_id) ); CREATE TABLE category_alias( id INTEGER PRIMARY KEY, category_id INTEGER, name STRING UNIQUE ); CREATE TABLE zones( id INTEGER PRIMARY KEY, zones INTEGER, zone INTEGER, UNIQUE(zones,zone) ); CREATE VIEW category_info AS SELECT category_id, item_id FROM category_item WHERE item_type_id=1; EXPLAIN QUERY PLAN SELECT infos.name, infos.id, infos.rating, DateTime(infos.date), DateTime(infos.expiration) FROM infos WHERE infos.id NOT IN ( SELECT DISTINCT infos.id FROM infos, category, category_info WHERE category_info.category_id=category.id AND category_info.item_id=infos.id AND (category.name in ('done','monthly','biweekly','weekly') OR category.id IN ( SELECT DISTINCT category_id FROM category_alias WHERE name in ('done','monthly','biweekly','weekly') ) ) AND category.zone IN (SELECT zone FROM zones WHERE zones.zones=5) ) AND (Date(date)='2014-07-20' OR Date(dateAdded)='2014-07-20') AND infos.zone IN (SELECT zone FROM zones WHERE zones.zones=1) GROUP BY infos.id ORDER BY date, rating; On Tue, Jul 29, 2014 at 4:55 AM, Michael <ruther1...@gmx-topmail.de> wrote: > Hello, > > The following query needs about 8 seconds since 3.8. In sqlite-3.7.17 it > was less than a second. > > select infos.name, infos.id, infos.rating, DateTime(infos.date), > DateTime(infos.expiration) > from infos > where infos.id not in > ( > select distinct infos.id from infos, category, category_info where > category_info.category_id=category.id and category_info.item_id=infos.id > and category.name in ('done','monthly','biweekly','weekly') > or > category.id in > ( > select distinct category_id from category_alias where name in > ('done','monthly','biweekly','weekly') > ) > and category.zone in (select zone from zones where zones.zones=5) > ) > and (Date(date)='2014-07-20' OR Date(dateAdded)='2014-07-20') > and infos.zone in (select zone from zones where zones.zones=1) > group by infos.id order by date, rating; > > Does anyone have an idea what's the problem with the query planer in > this case? > If I remove the part ... > or > category.id in () > ... it's mutch faster > > Should I post the EXPLAIN? > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users