Indexes on columns

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Indexes on columns

logan-20
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Indexes on columns

Dan Kennedy-4
On 06/24/2011 12:26 PM, [hidden email] 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Indexes on columns

logan-20
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 <[hidden email]> wrote:

> On 06/24/2011 12:26 PM, [hidden email] 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Indexes on columns

Igor Tandetnik
[hidden email] 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Indexes on columns

logan-20
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 <[hidden email]> wrote:

> [hidden email] 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Indexes on columns

Puneet Kishor-2

On Jun 24, 2011, at 1:58 PM, [hidden email] 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 <[hidden email]> wrote:
>
>> [hidden email] 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
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Indexes on columns

Igor Tandetnik
In reply to this post by logan-20
On 6/24/2011 1:58 PM, [hidden email]
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Indexes on columns

Simon Slavin-3
In reply to this post by logan-20

On 24 Jun 2011, at 6:58pm, [hidden email] 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Indexes on columns

logan-20
In reply to this post by Igor Tandetnik
Ahh..ok, thanks for the response everyone. I really appreciate the help here
:).

On Fri, Jun 24, 2011 at 11:10 AM, Igor Tandetnik <[hidden email]>wrote:

> On 6/24/2011 1:58 PM, [hidden email]
> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users