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 to create an index on a string?
>> Simon suggest
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 index, but I'm not
> sure w
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 the
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 ON reports
> ("yearz_doe
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
> when I use 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
(ye
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 software creates a large tab
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
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 t
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 look at each appl
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 performance gains and w
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
> >
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
___
sqlite-users mailing list
sqlite
> 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/delete
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 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, Simon, rows... n
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 process is much slower than it
> > n
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
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 whos values
> are all NULL take
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 turn. This
> would save on the ove
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 -
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
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 empty as it does on one in which al
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 don't
-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
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.
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
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
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 paren
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
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
fu
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
"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 th
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
34 matches
Mail list logo