I've seen this too! Had to refactor my "x IN (y)" code...
Perhaps the optimizer can be improved for this particular case? > Date: Tue, 27 Oct 2009 11:06:14 -0700 > From: [email protected] > To: [email protected] > Subject: [sqlite] Performance issues for "WITH x IN (y)" - fixed with "x = y" > > While doing performance optimizations for queries on a table with 100k rows > of email msg details and numerous indexes (Zimbra desktop email client), I > found that select queries including x IN (y) can have a severe speed issue, > especially when there is an ORDER by component as well. This is eliminated > by converting to ³x=y² which allows the query optimizer to pick the proper > indexes and ORDER BY when using ³explain query plain². > > Also, converting x IN (y, z) to (x = y OR x = z) and expanding for up to 4-5 > conditions is also faster by about 4 msec (5+% on a Macbook pro). After > about 7 conditions, IN becomes faster. > > Slow query: > > sqlite> explain query plan SELECT mi.id, mi.date AS sortcol FROM mail_item > AS mi WHERE ((type = 16 OR type = 5) AND mi.folder_id IN (2)) ORDER BY > sortcol DESC LIMIT 0,119; > 0|0|TABLE mail_item AS mi WITH INDEX i_mail_item_folder_id_date > > > Fast query: > > sqlite> explain query plan SELECT mi.id, mi.date AS sortcol FROM mail_item > AS mi WHERE ((type = 16 OR type = 5) AND mi.folder_id = 2) ORDER BY sortcol > DESC LIMIT 0,119; > 0|0|TABLE mail_item AS mi WITH INDEX i_mail_item_folder_id_date ORDER BY > > > Database definition: > > CREATE TABLE IF NOT EXISTS mail_item ( > id INTEGER UNSIGNED NOT NULL PRIMARY KEY, > type TINYINT NOT NULL, > parent_id INTEGER UNSIGNED, > folder_id INTEGER UNSIGNED, > index_id VARCHAR(225), > imap_id INTEGER UNSIGNED, > date INTEGER UNSIGNED NOT NULL, > size BIGINT UNSIGNED NOT NULL, > volume_id VARCHAR(255), > blob_digest VARCHAR(28), > unread INTEGER UNSIGNED, > flags INTEGER NOT NULL DEFAULT 0, > tags BIGINT NOT NULL DEFAULT 0, > sender VARCHAR(128), > subject TEXT, > name VARCHAR(128), > metadata MEDIUMTEXT, > mod_metadata INTEGER UNSIGNED NOT NULL, > change_date INTEGER UNSIGNED, > mod_content INTEGER UNSIGNED NOT NULL, > change_mask INTEGER UNSIGNED, > > CONSTRAINT fk_mail_item_parent_id FOREIGN KEY (parent_id) REFERENCES > mail_item(id), > CONSTRAINT fk_mail_item_folder_id FOREIGN KEY (folder_id) REFERENCES > mail_item(id) > ); > > CREATE INDEX IF NOT EXISTS i_mail_item_type ON mail_item(type); > CREATE INDEX IF NOT EXISTS i_mail_item_parent_id ON mail_item(parent_id); > CREATE INDEX IF NOT EXISTS i_mail_item_folder_id_date ON > mail_item(folder_id, date DESC); > CREATE INDEX IF NOT EXISTS i_mail_item_index_id ON mail_item(index_id); > CREATE INDEX IF NOT EXISTS i_mail_item_unread ON mail_item(unread); > CREATE INDEX IF NOT EXISTS i_mail_item_date ON mail_item(date DESC); > CREATE INDEX IF NOT EXISTS i_mail_item_mod_metadata ON > mail_item(mod_metadata); > CREATE INDEX IF NOT EXISTS i_mail_item_tags_date ON mail_item(tags, date > DESC); > CREATE INDEX IF NOT EXISTS i_mail_item_flags_date ON mail_item(flags, date > DESC); > CREATE INDEX IF NOT EXISTS i_mail_item_change_mask ON mail_item(change_mask, > date); > > > I am using sqlite 3.6.18. > > Thank you, > Todd Richmond > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _________________________________________________________________ Nya Windows 7 - Hitta en dator som passar dig! Mer information. http://windows.microsoft.com/shop _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
