On 31 Jul 2018, at 4:42pm, Rob Willett <rob.sql...@robertwillett.com> wrote:
> We have not checked that the order of columns in the index match the ORDER BY > clauses. We never thought of that either, That is going to make a big difference. Well done Gunter. Rob: don't forget that once you have your indexes defined and data in the tables, do an ANALYZE. For those who don't know, columns of an index should match first the columns mentioned in the WHERE clause, then the columns in the ORDER BY clause, in the order they are mentioned. For instance, except for very unusual data, SELECT * FROM MyTable WHERE y < 4 AND l = 16 ORDER BY a, t, r perfect index: (l, y, a, t, r) less useful: (l, y, r, a, t) less useful: (y, l, a, t, r) of little use: (a, t, r, l, y) Any index which starts with anything except l is almost useless, even though it mentions every column needed. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
