On Nov 26, 2012, at 12:30 AM, Jay A. Kreibich j...@kreibi.ch 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 look at
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
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 then
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 clem...@ladisch.de wrote:
Paul Sanderson wrote:
My software
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 -
On 25 Nov 2012, at 1:29pm, Paul Sanderson sandersonforens...@gmail.com 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 turn.
On 25 Nov 2012, at 1:29pm, Paul Sanderson sandersonforens...@gmail.com 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
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 all of
On Sun, Nov 25, 2012 at 04:56:44PM +, Simon Slavin scratched on the wall:
On 25 Nov 2012, at 1:29pm, Paul Sanderson sandersonforens...@gmail.com
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
On 25 Nov 2012, at 6:13pm, Jay A. Kreibich j...@kreibi.ch 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, Simon,
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 index on
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
insert/update/deletes,
On Nov 25, 2012, at 8:41 PM, Keith Medcalf kmedc...@dessus.com 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
___
sqlite-users
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
stack
On 25 Nov 2012, at 11:30pm, Jay A. Kreibich j...@kreibi.ch 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 performance
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
Paul Sanderson sandersonforens...@gmail.com 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 empty as
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 a
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.
-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 with
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 parent value as defined
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 UNIQUE,
Url text NOT
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 representation designed
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
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 ancestor(NodeID,1).
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 to
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 this?
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 it.
28 matches
Mail list logo
