SQLite indexes and how to use them

[RizlaUK]

ok its about time i learnt what a index is and how to use it

so this explains what a index is
http://www.sqlite.org/lang_createindex.html

but how do i read information from it

i want to index some feilds in the database that will all contain common information, country, city that sort of thing, i would usaly make a table with 2 feilds (id and textstring) and use the id in the other table to retrive the text string

would i benifit from useing indexes, as from what i read it dose the same thing and if so, how to use them in AMS

[bule]

You are missing the point of indexes, Rizla.

You don't use indexes directly, instead you only create them for the columns you choose from a table. You will probably want to index columns such as primary and foreign keys, columns which you often include in your where clauses and so on.

Once you create an index, it is automatically used by the SQLite engine when appropriate. If you do proper indexing, you will notice huge speed improvment in your select statements (especially those that join tables). The downside of the indexes is that insert, update and delete statements on the indexed tables take a very small bit longer (really irrelevant), and your database is a bit bigger (depends on how much indexes you define; data in the index is a duplicate of the data in a table). But the query speed is improved dramatically. As I said, SQLite will choose on it's own whether it will or not use an index when processing query (there are various optimizations involved in these decisions).

You don't manually fill the index. It is automatically maintained by the SQLite engine as you modify the source table.

Once you get into the indexes, get your mind on an another powerful thingy in SQL, and these would be triggers...

[RizlaUK]

ok, i see, i dont use the indexes at all, i add the indexex for speed inprovments and sqlite takes care of the rest, i just read and write as normal

so it would pay to index feilds that will be called often in "where" statments


ok, that makes sence, now on to triggers

[stickck]

Alright, i'm in.... i guess i need to learn about Triggers since i already use Indexes.

[bule]

So, triggers... imagine you have to manage parts in a mechanic's store.
(This will be very trivial example with no specific country laws being obeyed in process... :D)

You would have a table parts with (let's say) these columns:

PARTS:
id - unique id
name - part's name 
price - part's price
amount - amount in stock

Now, you would need an another table that would document changes of the amount of the parts in stock:

PART_CHANGES:
id - unique id
part_id - id of the part
reason - custom text about the change
change_datetime - datetime the change took place
amount_changed - amount changed at the stock (positive or negative number)

So, whenever you insert, update or delete a record in the PART_CHANGES table, you would want to update the PARTS table for the relevant part as well, right? You could do this manually each time on all the places you modify the database, but why when you could make use of triggers? It will be a bunch faster and you will be sure that you have the exact value. So, how to create a trigger that would do this for us? Well let the Bule show ya, ha? (BTW, you should create a trigger right after you create both the tables and before you fill any data to it so that you have the correct values in it. Otherwise, manually adjust the amount values).

Here is how to create three simple triggers that will take care of the amount column in the PARTS table as you modify the PART_CHANGES table (insert, update, delete):

create trigger part_changes_insert insert on part_changes
begin 
  update parts set amount = amount + new.amount_changed where new.amount_changed is not null and id=new.part_id;
end;

create trigger part_changes_update update on part_changes
begin 
  update parts set amount = amount - old.amount_changed where old.amount_changed is not null and id=old.part_id;
  update parts set amount = amount + new.amount_changed where new.amount_changed is not null and id=new.part_id;
end;

create trigger part_changes_delete delete on part_changes
begin 
  update parts set amount = amount - old.amount_changed where old.amount_changed is not null and id=old.part_id;
end;

The old. and new. prefixes in the code above are references to the row in the table part_changes being modified (before and after the change; as you can see, only for the update statement it makes sense to use both). We are directly updating parts table, so it's columns need no prefix. Also, please remember that at the moment the trigger fires, the record (in this example for the table part_changes) is not yet changed/saved in the database, and also, if you have a syntax error in your trigger, it will not be changed/saved at all!

And by the way, here are the indexes to optimize the access:

create index part_id ON parts (id);
create index partchan_id ON part_changes (id);
create index partchan_part_id ON part_changes (part_id);

[HMMurdock]

Thanks for the Trigger tutorial! That could prove quite useful.

Regarding Indexes:

I've had to teach several MS Access classes over the years and the subject of Indexes always comes up.

As mentioned before the database engine takes care of maintaining and utilizing the indexes once you've told it what to create, but other than that it is just like the index in the back of a text book...

1) It helps you jump to a specific item without having to parse the entire book (table)
2) A well designed index only has items that you are likely to search for
2b) Everything isn't indexed. If it was, the index would be bigger than the book (table)
3) While it makes searching faster, updates are a little bit slower because you need to update the index when you add any new data.

Basically indexes are a compromise between the time it takes to retrieve data and the time it takes to change data.

[bule]

While it makes searching faster, updates are a little bit slower because you need to update the index when you add any new data.

While the database has to write the data into the table and all the indexes of it, it doesn't necessary means the query will be slower. For example, take the query:

update part_changes set reason='no particular reason' where id=232344

Now, imagine the time needed to go through the entire non-indexed table to find the location of the record to update it?!

While the actual write time is a bit longer with the indexed table, the total query (search+write) time is a way shorter when compared to a non-indexed table. ;)

So, the only thing that is slower a puny bit is the insert statement, while the update and delete statements are usually faster due to common usage of the where clauses.

[RizlaUK]

@Blue, that really helps and can help right now, this app will need plenty of automation and that sounds like just what i need to do

Thanks

Basically indexes are a compromise between the time it takes to retrieve data and the time it takes to change data.

ok, well more adding than editing will be going on so i think i will index as suggested, my toughts are to just index any feilds im likely to search (keys and product titles)

Thanks for the SQLite lesson guys

Edit

Now, imagine the time needed to go through the entire non-indexed table to find the location of the record to update it?!

While the actual write time is a bit longer with the indexed table, the total query (search+write) time is a way shorter when compared to a non-indexed table.

Makes sence to me, editing 1 row proberly wouldent make a diffrance but if updateing 100's of rows then surely indexing pays off then

[stickck]

WoW. That going to take a little bit to wrap my head around. i'll have to mess with a sample to see exactly how it works

Thank Bule.

[bule]

that really helps and can help right now, this app will need plenty of automation and that sounds like just what i need to do...

I know the feeling. Now let's collaboratively pray for the soon release of the APMS update and:
- child window support
- grid object revision
- focusable buttons