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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
