5

I am building a mobile application using Sqlite.Net and I have come across the Indexed attribute. This is shown in the example here: https://github.com/praeclarum/sqlite-net#example-time

public class Stock
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [MaxLength(8)]
    public string Symbol { get; set; }
}

public class Valuation
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [Indexed]
    public int StockId { get; set; }
    public DateTime Time { get; set; }
    public decimal Price { get; set; }
}

as you can see the StockId column is marked as Indexed but when querying the tables it will surely be up to the Sqlite engine to determine the indexes and optimise querying.

So my question is what is the Indexed attribute used for and do I need it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JKennedy
  • 18,150
  • 17
  • 114
  • 198

1 Answers1

5

but when querying the tables it will surely be up to the Sqlite engine to determine the indexes

No, indexes have to be predefined and are maintained with Insert, Update and Delete. When a query runs, the engine can decide which indexes to use or ignore but not to create one.

So the [Indexed] attribute on the foreign key is a proper manual optimization.

H H
  • 263,252
  • 30
  • 330
  • 514
  • If I insert a new row into the `Valuation` table for example without defining the `Index` attribute, are you saying the `index` will not be updated on the Sqlite database? – JKennedy Sep 29 '16 at 11:20