On Wed, Oct 10, 2012 at 10:53 AM, Hamish Symington < [email protected]> wrote:
> > 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. > When you are comparing two columns that have different collating sequences, the collating sequence of the left operand is used for the comparison. See http://www.sqlite.org/datatype3.html#collation for additional information. In your schema, SaleItem_SaleUUID collates using NOCASE and Picture_UUID collates using BINARY. Hence, the second query above (the fast query) uses BINARY for comparing the two values in the ON clause and is thus able to use the BINARY index that exists on Picture_UUID: CREATE INDEX idx_Picture_UUID ON Picture(Picture_UUID); The first query above (the slow query) compares the two values using NOCASE, and is thus unable to use the idx_Picture_UUID index. If you change your index to be: CREATE INDEX idx_Picture_UUID ON Picture(Picture_UUID COLLATE nocase); Then the first query will become fast and the second one will be slow. You can create two separate indices, one with COLLATE BINARY and the other with COLLATE NOCASE, and then both queries will be fast. Note that I was mistaken earlier when I said that "X=Y" and "Y=X" should result in the same answer. If X and Y have different default collating sequences, then X=Y does not mean the same thing as Y=X and so the answers can be different. You can add a COLLATE clause after a comparison operator to explicitly state the collating sequence to be used. For example, "(X=Y collate binary)" instead of just "X=Y". -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
