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

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

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

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

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:

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

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

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

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...

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

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.

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

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

[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