Re: [sqlite] Indexes on columns
Ahh..ok, thanks for the response everyone. I really appreciate the help here :). On Fri, Jun 24, 2011 at 11:10 AM, Igor Tandetnik wrote: > On 6/24/2011 1:58 PM, logan...@gmail.com > wrote: > > Sorry, but seems like I'm missing something here. > > > > From my understanding it looks like for Integer ID columns that are PK > > SQLite doesn't generate any indexes. Is this true? > > It's true in a narrow technical sense, but it doesn't matter in practice. > > In SQLite, data is organized in B-trees. Each table and each index is a > B-tree. For an index, the key into that B-tree is the set of fields the > index is built on. For a table, each row has a unique integer > identifier, usually referred to as RowId, which serves as a key into the > table's B-tree. Looking up a row in the table by its RowId is as fast as > looking up an index entry by its key, because it's really the same > operation. > > When you declare a column as INTEGER PRIMARY KEY, SQlite simply makes it > an alias for an already-existing, always-present RowId column. Again, > the table itself essentially acts as an index on this column, no > additional external data structure is necessary. > -- > Igor Tandetnik > > ___ > 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] Indexes on columns
On 6/24/2011 1:58 PM, logan...@gmail.com wrote: > Sorry, but seems like I'm missing something here. > > From my understanding it looks like for Integer ID columns that are PK > SQLite doesn't generate any indexes. Is this true? It's true in a narrow technical sense, but it doesn't matter in practice. In SQLite, data is organized in B-trees. Each table and each index is a B-tree. For an index, the key into that B-tree is the set of fields the index is built on. For a table, each row has a unique integer identifier, usually referred to as RowId, which serves as a key into the table's B-tree. Looking up a row in the table by its RowId is as fast as looking up an index entry by its key, because it's really the same operation. When you declare a column as INTEGER PRIMARY KEY, SQlite simply makes it an alias for an already-existing, always-present RowId column. Again, the table itself essentially acts as an index on this column, no additional external data structure is necessary. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
On 24 Jun 2011, at 6:58pm, logan...@gmail.com wrote: > From my understanding it looks like for Integer ID columns that are PK > SQLite doesn't generate any indexes. Is this true? > > If the above is true then I want to create an index to improve the perf of > my queries that are run against it. The indexes are generated and SQLite will use them internally whenever it finds them convenient. It's just that they are not given names, so you can find out anything about them yourself. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
On Jun 24, 2011, at 1:58 PM, logan...@gmail.com wrote: > Sorry, but seems like I'm missing something here. > > From my understanding it looks like for Integer ID columns that are PK > SQLite doesn't generate any indexes. Is this true? > No, what you think is not true. SQLite does generate an index for INTEGER PRIMARY KEY columns. > If the above is true then I want to create an index to improve the perf of > my queries that are run against it. > > Thanks, > Hitesh > > On Fri, Jun 24, 2011 at 5:31 AM, Igor Tandetnik wrote: > >> logan...@gmail.com wrote: >>> Yes, that's exactly what it is. Here is the definition of one of the >> table: >>> >>> CREATE TABLE [Attributes] ( >>> [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, >>> [Name] VARCHAR(50) NOT NULL >>> ) >>> >>> Will creating explicit index on Id fix this issue? >> >> What issue? Why is having an explicit index, separate from that built into >> the table itself, important to you? What exactly do you feel is wrong with >> the way things are now? >> -- >> Igor Tandetnik >> >> ___ >> 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
Sorry, but seems like I'm missing something here. >From my understanding it looks like for Integer ID columns that are PK SQLite doesn't generate any indexes. Is this true? If the above is true then I want to create an index to improve the perf of my queries that are run against it. Thanks, Hitesh On Fri, Jun 24, 2011 at 5:31 AM, Igor Tandetnik wrote: > logan...@gmail.com wrote: > > Yes, that's exactly what it is. Here is the definition of one of the > table: > > > > CREATE TABLE [Attributes] ( > > [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > > [Name] VARCHAR(50) NOT NULL > > ) > > > > Will creating explicit index on Id fix this issue? > > What issue? Why is having an explicit index, separate from that built into > the table itself, important to you? What exactly do you feel is wrong with > the way things are now? > -- > Igor Tandetnik > > ___ > 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] Indexes on columns
logan...@gmail.com wrote: > Yes, that's exactly what it is. Here is the definition of one of the table: > > CREATE TABLE [Attributes] ( > [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > [Name] VARCHAR(50) NOT NULL > ) > > Will creating explicit index on Id fix this issue? What issue? Why is having an explicit index, separate from that built into the table itself, important to you? What exactly do you feel is wrong with the way things are now? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
Yes, that's exactly what it is. Here is the definition of one of the table: CREATE TABLE [Attributes] ( [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Name] VARCHAR(50) NOT NULL ) Will creating explicit index on Id fix this issue? Thanks. On Thu, Jun 23, 2011 at 11:07 PM, Dan Kennedy wrote: > On 06/24/2011 12:26 PM, logan...@gmail.com wrote: > > Hello, > > > > My understanding is that an index is automatically created on any column > > that is used in the primary key (or a composite index is created if the > key > > is composed of different columns). If this is correct then why don't I > see > > indexes for those in my table (I'm using SQLite Administrator and Firefox > > plugin based SQLite manager). I do see indexes for the columns that I > added > > a unique constraint upon. > > > > Is the above just a GUI error in these tools or an index need to be > created > > separately on the columns used in primary keys? > > Maybe your tables have "integer primary keys". Those are an exception > See here: > > http://www.sqlite.org/lang_createtable.html#rowid > > ___ > 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] Indexes on columns
On 06/24/2011 12:26 PM, logan...@gmail.com wrote: > Hello, > > My understanding is that an index is automatically created on any column > that is used in the primary key (or a composite index is created if the key > is composed of different columns). If this is correct then why don't I see > indexes for those in my table (I'm using SQLite Administrator and Firefox > plugin based SQLite manager). I do see indexes for the columns that I added > a unique constraint upon. > > Is the above just a GUI error in these tools or an index need to be created > separately on the columns used in primary keys? Maybe your tables have "integer primary keys". Those are an exception See here: http://www.sqlite.org/lang_createtable.html#rowid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexes on columns
Hello, My understanding is that an index is automatically created on any column that is used in the primary key (or a composite index is created if the key is composed of different columns). If this is correct then why don't I see indexes for those in my table (I'm using SQLite Administrator and Firefox plugin based SQLite manager). I do see indexes for the columns that I added a unique constraint upon. Is the above just a GUI error in these tools or an index need to be created separately on the columns used in primary keys? Thanks, Hitesh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
