Regarding Sqlite DB Index

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

Regarding Sqlite DB Index

Manoj Sengottuvel
Hi Richard,

If I create new table (table name : ACCOUNT_MSTR) then the auto index is
created as sqlite_autoindex_ACCOUNT_MSTR_1.

Then I am trying to execute the following query  ' select * from
sqlite_master where type='index';'

I got the following result


type

name

tbl_name

root_page

Sql

index

sqlite_autoindex_ACCOUNT_MSTR_1

ACCOUNT_MSTR

3

NULL

When I am seeing in the result sql column is NULL.

Why the sql column shows NULL value?

Do I need to create the index for the primary key?

But  the same case if I create my own index for the table (table name :
ACCOUNT_MSTR)  then I can able to see the  index creation script in sql
column .

I don't understand how the auto index is working?



Regards,

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

Re: Regarding Sqlite DB Index

J. King-3
On May 24, 2017 11:33:13 AM EDT, Manoj Sengottuvel <[hidden email]> wrote:

>Hi Richard,
>
>If I create new table (table name : ACCOUNT_MSTR) then the auto index
>is
>created as sqlite_autoindex_ACCOUNT_MSTR_1.
>
>Then I am trying to execute the following query  ' select * from
>sqlite_master where type='index';'
>
>I got the following result
>
>
>type
>
>name
>
>tbl_name
>
>root_page
>
>Sql
>
>index
>
>sqlite_autoindex_ACCOUNT_MSTR_1
>
>ACCOUNT_MSTR
>
>3
>
>NULL
>
>When I am seeing in the result sql column is NULL.
>
>Why the sql column shows NULL value?
>
>Do I need to create the index for the primary key?
>
>But  the same case if I create my own index for the table (table name :
>ACCOUNT_MSTR)  then I can able to see the  index creation script in sql
>column .
>
>I don't understand how the auto index is working?
>
>
>
>Regards,
>
>Manoj.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

The "sql" field contains the statement executed to create the table/index/trigger/etc. Automatic indexes are a side-effect of table creation, and so have no associated statements.
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Regarding Sqlite DB Index

Richard Hipp-3
In reply to this post by Manoj Sengottuvel
On 5/24/17, Manoj Sengottuvel <[hidden email]> wrote:
>
> If I create new table (table name : ACCOUNT_MSTR) then the auto index is
> created as sqlite_autoindex_ACCOUNT_MSTR_1.
>
> Then I am trying to execute the following query  ' select * from
> sqlite_master where type='index';'
>
> I got the following result [sqlite_master.sql field is NULL]
>

When a CREATE TABLE statement contains UNIQUE constraints, and
sometimes also PRIMARY KEYs (depending on the details of the PRIMARY
KEY), SQLite needs to create an index to support those features.  This
is done for you automatically.  You don't need to worry about it.

The sqlite_autoindex_ACCOUNT_MSTR_1 index is an example of such an index.

SQLite needs to know the location for the root b-tree page for this
index in order to use it.  For that reason, there is an entry in the
sqlite_master table for this index that records the rootpage.  But
there is no SQL associated with the index as it is created
automatically by the CREATE TABLE statement, so the sqlite_master.sql
for that entry is NULL.

All of the above is completely automatic.  There is no reason for you
to worry about any of it (unless you are hacking on the SQLite core).
It should not affect your usage of SQLite in any way.  You do not need
to understand it in order to use SQLite effectively  It just works.

The term "automatic index" is overloaded.  There are indexes
associated with UNIQUE and PRIMARY KEY constraints that are created
automatically, as described above.  There is a complete different
mechanism called "automatic indexes" that creates transient indexes
that only live for the duration of a single SELECT statement and which
are used to help optimize certain joins.  (They implement what MySQL
or Postgres would call a "hash join".)  Do not be confused by these
two completely different meanings for the term "automatic index".
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users