System.Data.SQLite

I have an application that's driven off of SQLite. It has many indexes for each table many of them seem to be used well but sometimes a simple query can lock up my app for a very long time. Usually in the order of minutes. Is there a way to see what indexes for a particular query SQLite decides to use and is there a way to force SQLite to use a certain index for a particular query. If the code is ADO.NET specific I'm completely fine with that. The application design was based around performance rather than extensibility so any System.Data.Sqlite specific code will be accepted.

Also runtime and compile time solutions are acceptable. The application work flows are restricted and predictable.

If you slap an 'EXPLAIN QUERY PLAN' before your query, it will show you high-level information about how it will perform your query.  This should show you the indices it is planning on using.

For more information, see: http://www.sqlite.org/lang_explain.html

If you are to the point of 'locking down' your database, and you need to ensure SQLite is using a specific index, there is a construct for this.  Be aware that unlike other database engines, it is not a hint but a requirement.  Your query will be forced to use this index, and if it cannot, it will fail.

For more information, see: http://www.sqlite.org/lang_indexedby.html

Hope this helps some.