Re: [sqlite] Creating indexes on non-existent columns with double quotes

On Wed, Jan 25, 2017 at 9:55 AM, Simon Slavin wrote: > > On 25 Jan 2017, at 5:45pm, Ersin Akinci wrote: > >> Thanks Richard and Simon for your insights. I think I'm still missing >> a few things, though. >> >> 1. What does it mean when SQLite tries

Re: [sqlite] Creating indexes on non-existent columns with double quotes

On 25 Jan 2017, at 5:45pm, Ersin Akinci wrote: > Thanks Richard and Simon for your insights. I think I'm still missing > a few things, though. > > 1. What does it mean when SQLite tries to create an index on a string? > Simon suggested that it's creating a calculated

Re: [sqlite] Creating indexes on non-existent columns with double quotes

Thanks Richard and Simon for your insights. I think I'm still missing a few things, though. 1. What does it mean when SQLite tries to create an index on a string? Simon suggested that it's creating a calculated index, but I'm not sure what means. (Does it just mean an "index" literally just on

Re: [sqlite] Creating indexes on non-existent columns with double quotes

On 23 Jan 2017, at 9:33pm, Ersin Akinci wrote: > CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports > (yearz_doesnt_exist); > CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports > ('yearz_doesnt_exist'); > CREATE INDEX index_reports_on_yearz_doesnt_exist

Re: [sqlite] Creating indexes on non-existent columns with double quotes

On 1/23/17, Ersin Akinci wrote: > CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports > ("yearz_doesnt_exist"); > > > I understand that the double quotation syntax is used to indicate > identifiers. Why am I allowed to create an index on a non-existent column >

[sqlite] Creating indexes on non-existent columns with double quotes

I'm trying to track down a behavior in SQLite that I don't fully understand and was hoping to get some help with. Here are three CREATE INDEX statements for a table called reports that does NOT have a column called yearz_doesnt_exist: CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports

Re: [sqlite] creating indexes on empty columns

Sorry - generally the sorts will be on one column - but they may choose at a later time to sort by another column. They will (but rarely - sort by two or more columns at the same time). On 26 November 2012 14:20, Clemens Ladisch wrote: > Paul Sanderson wrote: > > My

Re: [sqlite] creating indexes on empty columns

Paul Sanderson wrote: > My software creates a large table containing anything between about 250K > and Millions of rows when first run, the indexes are created immediately > after the table is populated and the tables do not change afterwards. > > The reason for the indexes is that the data is

Re: [sqlite] creating indexes on empty columns

Thanks for the replies - I'll try and read through them all thoroughly a bit later. But for now a bit of background. My software creates a large table containing anything between about 250K and Millions of rows when first run, the indexes are created immediately after the table is populated and

Re: [sqlite] creating indexes on empty columns

On Nov 26, 2012, at 12:30 AM, Jay A. Kreibich wrote: > "Using SQLite" (http://shop.oreilly.com/product/9780596521196.do) > has a very lengthy discussion of indexes and how they work, > specifically because it is difficult to generalize the use of > indexes. One must really

Re: [sqlite] creating indexes on empty columns

On 25 Nov 2012, at 11:30pm, "Jay A. Kreibich" wrote: > If you view an index as an optimization, then the idea > is usually to increase overall performance, so that there is net win. [snip] > > I disagree with this idea, as it implies there is a 1:1 exchange in > read

Re: [sqlite] creating indexes on empty columns

On Sun, Nov 25, 2012 at 12:41:21PM -0700, Keith Medcalf scratched on the wall: > > On Sunday, 25 November, 2012, 11:58, Jay A. Kreibich wrote: > > > each column is usually undesirable. A given SELECT can usually only > > use one index per query (or sub-query), so it rarely makes sense to > >

Re: [sqlite] creating indexes on empty columns

On Nov 25, 2012, at 8:41 PM, Keith Medcalf wrote: > (ie, be careful not to just add water into the bag -- the objective is to > poke it around, not just add more water) +1 for the, hmmm, treading water metaphore ___

Re: [sqlite] creating indexes on empty columns

> On Sunday, 25 November, 2012, 11:58, Jay A. Kreibich wrote: > each column is usually undesirable. A given SELECT can usually only > use one index per query (or sub-query), so it rarely makes sense to > stack up the indexes... adding unused indexes only slows down >

Re: [sqlite] creating indexes on empty columns

On Sun, Nov 25, 2012 at 01:29:48PM +, Paul Sanderson scratched on the wall: > Yes NULL - > > I underastand that ecvery coumn needs to be read, that is self evident, > however my feeling (not tested) is that the process is much slower than it > needs to be, i..e the process of creating an

Re: [sqlite] creating indexes on empty columns

On 25 Nov 2012, at 6:13pm, Jay A. Kreibich wrote: > On Sun, Nov 25, 2012 at 04:56:44PM +, Simon Slavin scratched on the wall: >> >> In SQLite, all columns are in all indexes even if the column contains a >> NULL. NULL has a sorting order, and anything that does > > Rows,

Re: [sqlite] creating indexes on empty columns

On Sun, Nov 25, 2012 at 04:56:44PM +, Simon Slavin scratched on the wall: > > On 25 Nov 2012, at 1:29pm, Paul Sanderson > wrote: > > > I underastand that ecvery coumn needs to be read, that is self evident, > > however my feeling (not tested) is that the

Re: [sqlite] creating indexes on empty columns

Paul Sanderson wrote: > I underastand that ecvery coumn needs to be read, that is self evident, > however my feeling (not tested) is that the process is much slower than it > needs to be, i..e the process of creating an index on a column whos values > are all NULL takes longer than just reading

Re: [sqlite] creating indexes on empty columns

On 25 Nov 2012, at 1:29pm, Paul Sanderson wrote: > I underastand that ecvery coumn needs to be read, that is self evident, > however my feeling (not tested) is that the process is much slower than it > needs to be, i..e the process of creating an index on a column

Re: [sqlite] creating indexes on empty columns

On 25 Nov 2012, at 1:29pm, Paul Sanderson wrote: > That does lead to another question. Is their a method of creating multiple > indexes at the same time, e.g. create an index on each (or specified) > column in a table in one pass - rather than do each column in

Re: [sqlite] creating indexes on empty columns

Yes NULL - I underastand that ecvery coumn needs to be read, that is self evident, however my feeling (not tested) is that the process is much slower than it needs to be, i..e the process of creating an index on a column whos values are all NULL takes longer than just reading all of the columns -

Re: [sqlite] creating indexes on empty columns

Paul Sanderson wrote: > Whilst building a new app I created an index on every column some of which > were empty. And with "empty", you mean that every value in that column is NULL? > The database is reasonably large (400K rows) and I notcied that > it seems to take as long to create an index on

Re: [sqlite] creating indexes on empty columns

Paul Sanderson wrote: > Whilst building a new app I created an index on every column some of which > were empty. The database is reasonably large (400K rows) and I notcied that > it seems to take as long to create an index on a column in which all the > rows are

[sqlite] creating indexes on empty columns

Whilst building a new app I created an index on every column some of which were empty. The database is reasonably large (400K rows) and I notcied that it seems to take as long to create an index on a column in which all the rows are empty as it does on one in which all the rows are unique. I

Re: [sqlite] creating indexes

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 alenD wrote: > Should an index be created before insertions or after insertions?:super: What performance difference did you see after trying both options? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG

[sqlite] creating indexes

Hi, Should an index be created before insertions or after insertions?:super: thanks in advance! _alenD -- View this message in context: http://old.nabble.com/creating-indexes-tp27299680p27299680.html Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] Creating Indexes

On Wed, 6 Aug 2008 16:45:35 -0400, Jeffrey Becker wrote: > On Wed, Aug 6, 2008 at 4:10 PM, > Kees Nuyt <[EMAIL PROTECTED]> wrote: >> Additionally: NodeID and ParentID shouldn't be blobs. >> Integer is the most suitable type for IDs. > >The blobs I'm inserting are actually a binary

Re: [sqlite] Creating Indexes

On Wed, Aug 6, 2008 at 4:10 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote: > On Tue, 05 Aug 2008 17:22:05 -0500, you wrote: > >>Jeffrey Becker wrote: >>> I have a table 'SiteMap' defined as : >>> >>> Create Table SiteMap >>> ( >>> NodeID blob not null PRIMARY KEY, >>> Title text NOT NULL

Re: [sqlite] Creating Indexes

On Tue, 05 Aug 2008 17:22:05 -0500, you wrote: >Jeffrey Becker wrote: >> I have a table 'SiteMap' defined as : >> >> Create Table SiteMap >> ( >> NodeID blob not null PRIMARY KEY, >> Title text NOT NULL UNIQUE, >> Url text NOT NULL >> ); >> >> I'd like to index on the node's

Re: [sqlite] Creating Indexes

Jeffrey Becker wrote: > I have a table 'SiteMap' defined as : > > Create Table SiteMap > ( > NodeID blob not null PRIMARY KEY, > Title text NOT NULL UNIQUE, > Url text NOT NULL > ); > > I'd like to index on the node's parent value as defined by the > expression

[sqlite] Creating Indexes

I have a table 'SiteMap' defined as : Create Table SiteMap ( NodeID blob not null PRIMARY KEY, Title text NOT NULL UNIQUE, Url text NOT NULL ); I'd like to index on the node's parent value as defined by the expression ancestor(NodeID,1). 'ancestor' being a user defined

Re: [sqlite] creating indexes in attached databases

On 8/19/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Jonathan Ellis" <[EMAIL PROTECTED]> wrote: > sqlite> attach 'foo.db' as foo; > sqlite> create table foo.bar (i int); > sqlite> create index foo.bar_i on foo.bar(i); Should be: create index foo.bar_i on bar(i); Thanks, that fixed

Re: [sqlite] creating indexes in attached databases

"Jonathan Ellis" <[EMAIL PROTECTED]> wrote: > sqlite> attach 'foo.db' as foo; > sqlite> create table foo.bar (i int); > sqlite> create index foo.bar_i on foo.bar(i); Should be: create index foo.bar_i on bar(i); > SQL error: near ".": syntax error > sqlite> > > Is there another way to do

[sqlite] creating indexes in attached databases

sqlite> attach 'foo.db' as foo; sqlite> create table foo.bar (i int); sqlite> create index foo.bar_i on foo.bar(i); SQL error: near ".": syntax error sqlite> Is there another way to do this? - To unsubscribe, send email