On Tue, Aug 16, 2011 at 2:54 PM, Filip Navara <filip.nav...@gmail.com> wrote:
> On Tue, Aug 16, 2011 at 2:24 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>>
>> On 16 Aug 2011, at 9:39am, Filip Navara wrote:
>>
>>> create table a (a, b, c, d);
>>> create index aIdx on a (a, d);
>>>
>>> Now the data in columns "b" and "c" can be pretty large. Let's make
>>> the following query:
>>>
>>> select a, d from a;
>>>
>>> Full-table scan is done even if an index exists that covers all the
>>> data required by the query.
>>
>> That's a little strange.
>>
>> Please execute the SQL command ANALYZE on that database. Then do your
>> 'EXPLAIN' tests again. It won't fix the problem but it'll tell us something
>> useful.
>>
>> http://www.sqlite.org/lang_analyze.html
>
> It doesn't fix anything. It's not even possible to force the index
> usage using INDEXED BY.
>
I forgot to attach the data, sorry. These are the real queries on the
actual database data.
attach 'mail_index.dat' as mail_index;
attach 'folders.dat' as folders;
-- Schema --
CREATE TABLE mail_index."MailItems" (
"id" INTEGER NOT NULL PRIMARY KEY,
"uniqueId",
"versionId",
"syncFlags" INTEGER,
"folder" INTEGER,
"syncFolder" INTEGER,
"date" TIMESTAMP,
"subject" TEXT,
"inReplyTo" TEXT,
"messageId" TEXT,
"importance" INTEGER,
"account" TEXT,
"flags" INTEGER,
"type" INTEGER,
"size" INTEGER,
"editTime" INTEGER,
"operationsPerformed" INTEGER,
"receivedDate" TIMESTAMP,
"replyDate" TIMESTAMP,
"forwardDate" TIMESTAMP, "references" TEXT);
CREATE INDEX mail_index."idx_MailItems" ON "MailItems" ("folder", "id");
CREATE INDEX mail_index."idx_MailItems_accountAndFlags" ON "MailItems"
("account", "flags");
CREATE INDEX mail_index."idx_MailItems_flags" ON "MailItems"
("folder", "flags", "syncFlags");
CREATE INDEX mail_index."idx_MailItems_folderAndAccountAndFlags" ON
"MailItems" ("folder", "account", "flags");
CREATE INDEX mail_index."idx_MailItems_uniqueId" ON "MailItems"
("folder", "syncFolder", "uniqueId");
CREATE TABLE folders."Folders" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"uniqueId",
"versionId",
"flags" INTEGER DEFAULT 0,
"name" TEXT,
"delimiter" CHAR DEFAULT '/',
"path" TEXT COLLATE NOCASE,
"parentFolderId" INTEGER);
CREATE INDEX folders.idx_Folders_idAndFlags ON "Folders" ("id", "flags");
CREATE INDEX folders.idx_Folders_parentAndFlags ON "Folders"
("parentFolderId", "flags");
CREATE UNIQUE INDEX folders.idx_Folders_path ON "Folders" ("path");
-- Before ANALYZE --
SELECT COUNT(*) FROM (SELECT id FROM mail_index.MailItems WHERE NOT
("flags" & 2) AND NOT ("flags" & 65536) AND "folder" not in
(32,301,140,160,187,185,273,270,281,478,481,7,6) AND "folder" not in
(SELECT "id" FROM folders.Folders WHERE ("flags" & 8)));
155963
-- Plain query
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,301,140,160,187,185,273,270,281,478,481,7,6)
AND "folder" not in (SELECT "id" FROM folders.Folders WHERE ("flags"
& 8)));
0|0|0|SCAN TABLE MailItems (~62500 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SCAN TABLE Folders (~500000 rows)
-- Added ORDER BY
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,301,140,160,187,185,273,270,281,478,481,7,6)
AND "folder" not in (SELECT "id" FROM folders.Folders WHERE ("flags"
& 8)) ORDER BY "folder");
1|0|0|SCAN TABLE MailItems USING COVERING INDEX
idx_MailItems_folderAndAccountAndFlags (~62500 rows)
1|0|0|EXECUTE LIST SUBQUERY 2
1|0|0|EXECUTE LIST SUBQUERY 2
2|0|0|SCAN TABLE Folders (~500000 rows)
0|0|0|SCAN SUBQUERY 1 (~62500 rows)
-- Added "folder" > 0
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,301,140,160,187,185,273,270,281,478,481,7,6)
AND "folder" not in (SELECT "id" FROM folders.Folders WHERE ("flags"
& 8)) AND "folder" > 0);
0|0|0|SEARCH TABLE MailItems USING COVERING INDEX
idx_MailItems_folderAndAccountAndFlags (folder>?) (~15625 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SCAN TABLE Folders (~500000 rows)
-- After ANALYZE --
-- Plain query
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,301,140,160,187,185,273,270,281,478,481,7,6)
AND "folder" not in (SELECT "id" FROM folders.Folders WHERE ("flags"
& 8)));
0|0|0|SCAN TABLE MailItems (~10986 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SCAN TABLE Folders (~167 rows)
-- Added ORDER BY
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,301,140,160,187,185,273,270,281,478,481,7,6)
AND "folder" not in (SELECT "id" FROM folders.Folders WHERE ("flags"
& 8)) ORDER BY "folder");
1|0|0|SCAN TABLE MailItems USING COVERING INDEX idx_MailItems_folderAndAccountAn
dFlags (~10986 rows)
1|0|0|EXECUTE LIST SUBQUERY 2
1|0|0|EXECUTE LIST SUBQUERY 2
2|0|0|SCAN TABLE Folders (~167 rows)
0|0|0|SCAN SUBQUERY 1 (~10986 rows)
-- Added "folder" > 0
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,3
01,140,160,187,185,273,270,281,478,481,7,6) AND "folder" not in
(SELECT "id" FROM folders.Folders WHERE ("flags" & 8)) AND "folder" >
0);
0|0|0|SEARCH TABLE MailItems USING COVERING INDEX idx_MailItems_folderAndAccount
AndFlags (folder>?) (~2746 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SCAN TABLE Folders (~167 rows)
Best regards,
Filip Navara
Best regards,
Filip Navara
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
