On Thu, Feb 09, 2012 at 02:05:53PM +0000, Simon Slavin wrote: > > On 9 Feb 2012, at 1:45pm, Valentin Davydov wrote: > > > CREATE TABLE T(X, Y, Z, ..., UNIQUE (X, Y, Z, ...) ON CONFLICT IGNORE); > > CREATE INDEX IX ON T(X); > > CREATE INDEX IY ON T(Y); > > CREATE INDEX IZ ON T(Z); > > ..... > > Not an answer to your question, but it's worth knowing that those > indexes may not be helping anything you're doing.
Perhaps IX is indeed redundant (because it should coinside with the beginning of the implicit uniqueness index), but other indices are quite useful in doing quick selects of small subsets of data (e.g. SELECT MAX(Z) FROM T;), which is typical usage pattern in my application. >SQLite already has to create an index idea for coping with your UNIQUE >clause. So it already has an index on > > (X, Y, Z, ...) But this index is useless in searches which don't iclude X column. > Try removing those indexes and see if this improves matters. > It'll certainly make your filesize smaller. Not so significant. They altogether use up no more than one third of the total space, two other thirds being occupied by the table itself and by the implicit index respectively. Actually even less, because some columns don't have individual indices. Valentin Davydov. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
