Hello, I've made a new database with the latest version of SQLite, and added two tables, created thus:
CREATE TABLE SaleItem (SaleItem_ID integer NOT NULL PRIMARY KEY, SaleItem_UUID varchar NOT NULL DEFAULT '', SaleItem_SaleUUID varchar NOT NULL DEFAULT '', SaleItem_PictureUUID varchar NOT NULL DEFAULT '' COLLATE NOCASE); and CREATE TABLE Picture (Picture_ID integer NOT NULL PRIMARY KEY, Picture_UUID varchar NOT NULL DEFAULT '', Picture_FileName varchar NOT NULL DEFAULT '' COLLATE NOCASE); I also have three indexes - one each on SaleItem_UUID, Picture_UUID and SaleItem_PictureUUID. In SaleItem, I have 8500 rows. In Picture, I have about 1 million. For the sake of ease, let's say that every four SaleItem rows have the same SaleItem_SaleUUID, and that each SaleItem has a different, random Picture_UUID in its SaleItem_PictureUUID field. If I perform the query SELECT SaleItem.*, Picture_FileName FROM SaleItem LEFT JOIN Picture ON SaleItem_PictureUUID=Picture_UUID WHERE SaleItem_SaleUUID = 'DAB8FE97- D308-4809-B496-E55142DC05B5' the query is slow (c. 11 sec) but if I perform the exact same query but with the order of the terms in the ON part switched SELECT SaleItem.*, Picture_FileName FROM SaleItem LEFT JOIN Picture ON Picture_UUID=SaleItem_PictureUUID WHERE SaleItem_SaleUUID = 'DAB8FE97- D308-4809-B496-E55142DC05B5' then the query is near instant. Doing an EXPLAIN QUERY PLAN gives me 0 0 0 SEARCH TABLE SaleItem USING INDEX idx_SaleItem_PictureUUID (SaleItem_SaleUUID=?) (~10 rows) 0 1 1 SEARCH TABLE Picture USING AUTOMATIC COVERING INDEX (Picture_UUID=?) (~10 rows) for the slow one and 0 0 0 SEARCH TABLE SaleItem USING INDEX idx_SaleItem_PictureUUID (SaleItem_SaleUUID=?) (~10 rows) 0 1 1 SEARCH TABLE Picture USING INDEX idx_Picture_UUID (Picture_UUID=?) (~10 rows) for the fast one. I was under the impression that the order of fields in the ON clause doesn't matter. Is this impression incorrect? Thanks, Hamish _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
