[sqlite] Max of 63 columns for a covering index to work?
I'm experimenting with covering indices on one of our larger tables. I started seeing really inconsistent behavior, and made the following sample setup code that demonstrates it: DROP TABLE IF EXISTS test; CREATE TABLE test( col01 integer,col02 integer,col03 integer,col04 integer,col05 integer,col06 integer,col07 integer,col08 integer,col09 integer,col10 integer, col11 integer,col12 integer,col13 integer,col14 integer,col15 integer,col16 integer,col17 integer,col18 integer,col19 integer,col20 integer, col21 integer,col22 integer,col23 integer,col24 integer,col25 integer,col26 integer,col27 integer,col28 integer,col29 integer,col30 integer, col31 integer,col32 integer,col33 integer,col34 integer,col35 integer,col36 integer,col37 integer,col38 integer,col39 integer,col40 integer, col41 integer,col42 integer,col43 integer,col44 integer,col45 integer,col46 integer,col47 integer,col48 integer,col49 integer,col50 integer, col51 integer,col52 integer,col53 integer,col54 integer,col55 integer,col56 integer,col57 integer,col58 integer,col59 integer,col60 integer, col61 integer,col62 integer,col63 integer,col64 integer,col65 integer,col66 integer,col67 integer,col68 integer,col69 integer,col70 integer ); CREATE INDEX test1 ON test(col01,col02,col03); CREATE INDEX test2 ON test(col01,col02,col63); CREATE INDEX test3 ON test(col62,col63,col64); CREATE INDEX test4 ON test(col64,col65,col66); CREATE INDEX test5 ON test(col66,col67,col10); --With that initial setup, here's a pile of sample EXPLAIN QUERY PLANs and their result, as to whether the covering index works: EXPLAIN QUERY PLAN SELECT SUM(col03) FROM test WHERE col01=0 AND col02=1; --test1 COVERING EXPLAIN QUERY PLAN SELECT SUM(col63) FROM test WHERE col01=0 AND col02=1; --test2 COVERING EXPLAIN QUERY PLAN SELECT col02,col63 FROM test WHERE col01=0; --test2 COVERING EXPLAIN QUERY PLAN SELECT SUM(col64) FROM test WHERE col62=0 AND col63=1; --test3 NONCOVERING EXPLAIN QUERY PLAN SELECT col63 FROM test WHERE col62=0; --test3 COVERING EXPLAIN QUERY PLAN SELECT col63,col64 FROM test WHERE col62=0; --test3 NONCOVERING EXPLAIN QUERY PLAN SELECT SUM(col66) FROM test WHERE col64=0 AND col65=1; --test4 NONCOVERING EXPLAIN QUERY PLAN SELECT SUM(col10) FROM test WHERE col66=0 AND col67=1; --test5 NONCOVERING EXPLAIN QUERY PLAN SELECT col67,col10 FROM test WHERE col66=0; --test5 NONCOVERING Help? :) We'd really rather keep our table denormed, as we really do pull and use all >64 columns of data with every query, but if this is just a limitation, then we'll have to figure out a solution. It's not listed anywhere on the limits page, though, so I'm wondering if this needs to be added, or if I'm just doing something stupid or something. :) Thanks! -David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Max of 63 columns for a covering index to work?
On Wed, May 22, 2013 at 2:37 PM, David de Regt 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
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 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
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 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
Re: [sqlite] Max of 63 columns for a covering index to work?
On Wed, May 22, 2013 at 3:01 PM, Marc L. Allen 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 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
Re: [sqlite] Max of 63 columns for a covering index to work?
On May 22, 2013, at 8:58 PM, David de Regt wrote: > Back to the trenches to rearchitect this… Perhaps an opportunity to introduce bitmap indexes to SQLite… which would render compound indexes a thing of the past for certain class of problems such as yours... http://en.wikipedia.org/wiki/Bitmap_index Looking forward to it :D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 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
Re: [sqlite] Max of 63 columns for a covering index to work?
On 22 May 2013, at 7:58pm, David de Regt wrote: > if I have a 300 column table I'm going to sound my customary note of caution here. Do you really have a 300 column table or is it several thinner tables which have the same primary key ? Or do you really have a property list which should be one thinner table with a two-column primary key ? Generally in database design you should be able to hold a table schema in your head. When you find yourself numbering columns it's usually a sign you're doing something wrong. Not true in every case, of course, and you may have one of the incredibly rare cases which really is best represented with a 300 column table. In which case, please excuse me. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Max of 63 columns for a covering index to work?
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 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 o
Re: [sqlite] Max of 63 columns for a covering index to work?
I haven't had a table that large, but I have had big ones... the disadvantage is the number of records you can scan in a single disk read, but an advantage is that you don't have to take the time to join tables, especially when you need to do it ALL THE TIME. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, May 22, 2013 3:11 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? On 22 May 2013, at 7:58pm, David de Regt wrote: > if I have a 300 column table I'm going to sound my customary note of caution here. Do you really have a 300 column table or is it several thinner tables which have the same primary key ? Or do you really have a property list which should be one thinner table with a two-column primary key ? Generally in database design you should be able to hold a table schema in your head. When you find yourself numbering columns it's usually a sign you're doing something wrong. Not true in every case, of course, and you may have one of the incredibly rare cases which really is best represented with a 300 column table. In which case, please excuse me. Simon. ___ 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
Re: [sqlite] Max of 63 columns for a covering index to work?
Yeah, the index maintenance is enormous, but it means that the select times are fast. Medical info systems are usually pretty heavily oriented toward fast read operations. Our table here has a set of repeated substructures (8-column structures that we have 6 of, with certain fields used on each, since the type isn't fully repeating.) However, we use the whole subset of fields the vast majority of times we pull it, and so it's much faster to pull it out of the flat structure into our internal structure instead of doing the top select, then selecting all the subtypes. The reverse, on insert, is true as well. One insert is better than a master insert and then blowing away a bunch of subtable rows and then re-inserting. We're changing our data model generation tool to handle all of the indexes on our schema, and to make sure to output all of those first in the table generation order. That'll keep us under 63 for now... -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:11 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? 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 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
Re: [sqlite] Max of 63 columns for a covering index to work?
On Wed, May 22, 2013 at 9:11 PM, Marc L. Allen wrote: > [...]. It makes me think you might be better off using triggers to > maintain separate tables with covered data instead of indexes. [...]. > This sounds like Oracle's materialized views to me, which come in synchronous (trigger-based) or asynchronous (log-mining-based) variants. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Max of 63 columns for a covering index to work?
I'm not familiar with that. It's a "view" where Oracle actually stores the view data as a physical table? And updates these tables as the main table updates? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, May 23, 2013 8:57 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 9:11 PM, Marc L. Allen wrote: > [...]. It makes me think you might be better off using triggers to > maintain separate tables with covered data instead of indexes. [...]. > This sounds like Oracle's materialized views to me, which come in synchronous (trigger-based) or asynchronous (log-mining-based) variants. ___ 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
Re: [sqlite] Max of 63 columns for a covering index to work?
On Thu, May 23, 2013 at 3:02 PM, Marc L. Allen wrote: > I'm not familiar with that. It's a "view" where Oracle actually stores > the view data as a physical table? And updates these tables as the main > table updates? Pretty much. And the query optimizer is aware of the relationship of course, and queries on the main table can be optionally routed to the mat-view. (see http://docs.oracle.com/cd/E11882_01/server.112/e25554/qradv.htm) And if you're willing to have a slight delay for the updating of the mat-view (async mode), you don't slow down inserts on the main table, and background processes (or threads on windows) "mine" the undo/redo logs to update the mat-view. In sync mode, you don't write the trigger, that's done for you, you just define the view as usual. But I'm no expert on the subject, just sharing perspective from the non-lite point of view, that's all. See http://docs.oracle.com/cd/E16338_01/server.112/e10706/repmview.htm for details. Here it's "Enable Data Subsetting" I guess. Closing the off-topic aside now --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
