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 <[email protected]> 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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
