Thanks Simon, that makes a lot of sense. Does the order of columns in a WHERE clause matter, or will the query optimizer look at them as a set and find the best index? (ignoring all the special cases)
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, August 27, 2013 7:29 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] CREATE INDEX and column order On 27 Aug 2013, at 1:07pm, Doug Nebeker <ad...@poweradmin.com> wrote: > I was reading about the new query planner and came across a few references to > that idea that the left most columns in the index definition should be the > most unique (as far as values in the column are concerned). > > Is that correct? In my case, many tables have a timestamp column, and I've > been using that as my right-most column, but it seems it would be a great > candidate to be switched. When using an index, SQL has to work from the most significant end -- the left -- to the least significant end -- the right. For instance, suppose you have a phone book CREATE TABLE phonebook (firstname TEXT, surname TEXT, phonenumber TEXT) CREATE INDEX psf ON phonebook (surname, firstname) This index is useless for looking someone up by their firstname, because it has everyone listed in surname order: Abelson, David Abelson, Joan Smith, David Smith, Martine Smith, Tom If you wanted to look up all the 'Martines' you'd just have to look through the whole index anyway. You might as well scan the original table. [1] Simon. [1] Yes, many picky details about this but I'm simplifying for the purpose of explanation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
