I was just thinking of that... if you have needs to dozens of covering indexes, then the index maintenance anytime you modify the table must be enormous. It makes me think you might be better off using triggers to maintain separate tables with covered data instead of indexes. The only downside to that, I suppose, is that you have to pick the right table when doing the select.
-----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 3:07 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? Correct. However, we have a pile of different uses on this table. I'm trying to evaluate if we can move all covering index columns into the first 63, but I'm not sure it's going to work, especially long term as we continue to grow the data. We'll see... In the medical industry I used to work in, there were commonly huge denorm "event" tables in multiple massive information systems, with covering indexes on several dozen different sets of large numbers of columns. With 100+ million records in the table, it was the only way to read from it in a performant fashion. -David -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Wednesday, May 22, 2013 12:02 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? 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?). 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 _______________________________________________ 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
