Hi, all.
Please, check following query plans.
It makes different query plans between 3.7.13 and 3.8.0.2.
It doesn't matter in common cases.
But, when I insert almost 10,000 entries on this table, below query runs
slightly slower on 3.8.0.2.
Regards,
Yongil.
====================== database schema =============================
CREATE TABLE files (_id INTEGER PRIMARY KEY AUTOINCREMENT,_data TEXT,_size
INTEGER,format INTEGER,parent INTEGER,date_added INTEGER,date_modified
INTEGER,mime_type TEXT,title TEXT,description TEXT,_display_name
TEXT,picasa_id TEXT,orientation INTEGER,latitude DOUBLE,longitude
DOUBLE,datetaken INTEGER,mini_thumb_magic INTEGER,bucket_id
TEXT,bucket_display_name TEXT,isprivate INTEGER,title_key TEXT,artist_id
INTEGER,album_id INTEGER,composer TEXT,track INTEGER,year INTEGER
CHECK(year!=0),is_ringtone INTEGER,is_music INTEGER,is_alarm
INTEGER,is_notification INTEGER,is_podcast INTEGER,album_artist
TEXT,folder_id INTEGER NOT NULL DEFAULT 0,date_played INTEGER DEFAULT
0,count_played INTEGER DEFAULT 0,is_favorite INTEGER DEFAULT 0,index_key
TEXT,duration INTEGER,bookmark INTEGER,artist TEXT,album TEXT,resolution
TEXT,tags TEXT,category TEXT,language TEXT,mini_thumb_data TEXT,name
TEXT,media_type INTEGER,old_id INTEGER, storage_id INTEGER, is_drm INTEGER,
width INTEGER, height INTEGER, video_filetype TEXT, video_iswatched INTEGER
DEFAULT 0, protected_type INTEGER DEFAULT 0);
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES('files','path_index_lower','9177 1');
INSERT INTO "sqlite_stat1" VALUES('files','format_index','9177 1148');
INSERT INTO "sqlite_stat1" VALUES('files','parent_index','9177 230');
INSERT INTO "sqlite_stat1" VALUES('files','bucket_name','9177 224 214 214');
INSERT INTO "sqlite_stat1" VALUES('files','bucket_index','9177 224 214 8
1');
INSERT INTO "sqlite_stat1" VALUES('files','folder_id_idx','9177 3059');
INSERT INTO "sqlite_stat1" VALUES('files','artist_id_idx','9177 3059');
INSERT INTO "sqlite_stat1" VALUES('files','titlekey_index','9177 4');
INSERT INTO "sqlite_stat1" VALUES('files','sort_index','9177 8 1');
INSERT INTO "sqlite_stat1" VALUES('files','album_id_idx','9177 3059');
INSERT INTO "sqlite_stat1" VALUES('files','title_idx','9177 2');
INSERT INTO "sqlite_stat1" VALUES('files','media_type_index','9177 1836');
INSERT INTO "sqlite_stat1" VALUES('files','path_index','9177 1');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('files',9261);
CREATE INDEX path_index ON files(_data);
CREATE INDEX media_type_index ON files(media_type);
CREATE INDEX title_idx ON files(title);
CREATE INDEX album_id_idx ON files(album_id);
CREATE INDEX sort_index ON files(datetaken ASC, _id ASC);
CREATE INDEX titlekey_index ON files(title_key);
CREATE INDEX artist_id_idx ON files(artist_id);
CREATE INDEX folder_id_idx on files(folder_id);
CREATE INDEX bucket_index on files(bucket_id, media_type, datetaken, _id);
CREATE INDEX bucket_name on files(bucket_id, media_type,
bucket_display_name);
CREATE INDEX parent_index ON files(parent);
CREATE INDEX format_index ON files(format);
CREATE INDEX path_index_lower ON files(_data COLLATE NOCASE);
================================ query =========================
SELECT bucket_id, media_type, bucket_display_name, _data FROM files WHERE
(storage_id!=131073) AND ((media_type=1 OR media_type=2)) GROUP BY 1,(2)
ORDER BY bucket_display_name COLLATE NOCASE ASC;
============================= query plans =========================
[3.7.13]
0|0|0|SCAN TABLE files USING INDEX bucket_name (~4588 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
[3.8.0.2]
0|0|0|SEARCH TABLE files USING INDEX media_type_index (media_type=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
