Hi Marco,
In reply to:
> From the PRAGMA index_info documentation:
> This pragma returns one row each column in the named index. The first column
> of the result is the rank of the column within the index. The second column
> of the result is the rank of the column within the table. The third column of
> output is the name of the column being indexed.
>
> I am particularly interested in the rank fields … what does rank really means
> here?
> Is there a lower/upper bound to this rank value?
If you have an index that is for multiple columns, the "rank of the column
within the index" (which is labeled in the output as "seqno") tells you the
order of those columns in the index. For example:
create table Person
( ID integer primary key not null
, "First Name" text collate nocase
, "Last Name" text collate nocase
, "Email" text collate nocase
)
;
create index "Person index by First, Last"
on "Person" ("First Name", "Last Name")
;
create index "Person index by Email, First"
on "Person" ("Email", "First Name")
;
Then:
pragma index_info ('Person index by First, Last')
gives:
seqno cid name
0 1 First Name
1 2 Last Name
which means that this indexes columns "First Name" and "Last Name" in that
order (ie 0, 1).
And
pragma index_info ('Person index by Email, First')
gives:
seqno cid name
0 3 Email
1 1 First Name
which means that this indexes columns "Email" and "First Name" in that order
(ie 0, 1).
Tom
Tom Brodhurst-Hill
BareFeetWare
--
iPhone/iPad/iPod and Mac software development, specialising in databases
[email protected]
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
