On 10/19/2018 02:30 AM, Deon Brewis wrote:
Hi,

I seem to have run into a limit where SQLITE doesn't use an index correctly if 
an indexed column is over the 64th column in the table. It's a partial index 
like:

CREATE INDEX idx ON
  table(A, B DESC, C, D)
  WHERE A > 0

Where A and B are columns 70 and 72 on 'table'.

I know about the 64-column limitation for covering indexes:
http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html

However, this isn't a covering index, it's a partial index. But it seems to run into the same 
limit. Even if I forced in the index into a query it still does a "USE TEMP B-TREE" at 
the end to satisfy a simple "ORDER BY A, B DESC" query. After I re-ordered the table, it 
magically started working.


Can you post an SQL script that demonstrates the problem?

Running the script below here, the partial index is used to optimize the ORDER BY in the query.

Thanks,
Dan.




CREATE TABLE t1(
  c0, c1, c2, c3, c4, c5, c6, c7, c8, c9,
  c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
  c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
  c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
  c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,
  c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,
  c60, c61, c62, c63, c64, c65, c66, c67, c68, c69,
  c70, c71, c72, c73, c74, c75, c76, c77, c78, c79,
  c80, c81, c82, c83, c84, c85, c86, c87, c88, c89
);

CREATE INDEX i1 ON t1(c80, c81 DESC, c82, c83) WHERE c80>0;
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c80>0 ORDER BY c80, c81 DESC;




_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to