On Wed, May 22, 2013 at 3:01 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote:
> I think there might be a disconnect. > > You can have a covering index on a 300 column table... it just can't cover > any column past the 63rd (or 64th?). > 63rd. The 64th bit is catch-all used to mean that some column past the 63rd is used. > > It's not perfect, but not as bad as not being able to have a covering > index at all. > > At least, that's how I read some of the answers. > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of David de Regt > Sent: Wednesday, May 22, 2013 2:59 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Max of 63 columns for a covering index to work? > > Hm. That's a wee bit of an issue for us, then. May want to stick that on > the limitations page... :) > > It seems like covering indexes become increasingly useful the more columns > you have on a table. When I have a 4-column table, if my covering index > uses 3 columns, that's not as big a read savings as if I have a 300 column > table that I only need to handle 3 columns from in a WHERE, and it > otherwise needs to pull the row/page from the original table to get the > value on. > > Back to the trenches to rearchitect this... > > Thanks for the quick clarification. :) > > -David > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Wednesday, May 22, 2013 11:53 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Max of 63 columns for a covering index to work? > > On Wed, May 22, 2013 at 2:37 PM, David de Regt <dav...@mylollc.com> wrote: > > > I'm experimenting with covering indices on one of our larger tables. > > > > *[many words expressing concern that SQLlite does not use covering > > indices on tables with more than 63 colums]...* > > > > > Your observations are correct. If a query uses any column of a table past > the 63rd column, then that query cannot use a covering index on that table. > This is due to the use of 64-bit unsigned integer bitmasks to keep track > of which columns have been used in order to discover whether or not a > covering index will work. > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > 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 > > > This email and any attachments are only for use by the intended > recipient(s) and may contain legally privileged, confidential, proprietary > or otherwise private information. Any unauthorized use, reproduction, > dissemination, distribution or other disclosure of the contents of this > e-mail or its attachments is strictly prohibited. If you have received this > email in error, please notify the sender immediately and delete the > original. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
