How to introduce indexing to sqlite query in android?

user_3442437 0
Viewed 97

In my android application, I use Cursor c = db.rawQuery(query, null); to query data from a local sqlite database, and one of the query string looks like the following:

SELECT t1.* FROM table t1
WHERE NOT EXISTS (
  SELECT 1 FROM table t2
  WHERE t2.start_time = t1.start_time AND t2.stop_time > t1.stop_time
)

however, the issue is that the query gets very slow when the database gets huge. Trying to look into introducing indexing to speed up the query, but so far, not been very successful, therefore, would be great to have some help here, as it's also hard to find examples for this for android applications.

android-sqlite indexing sqlite
1 Answers

You can create a composite index for the columns start_time and stop_time:

CREATE INDEX idx_name ON table_name(start_time, stop_time);

You can read in The SQLite Query Optimizer Overview:

The ON and USING clauses of an inner join are converted into additional terms of the WHERE clause prior to WHERE clause analysis ...

and:

If an index is created using a statement like this:

CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);

Then the index might be used if the initial columns of the index (columns a, b, and so forth) appear in WHERE clause terms. The initial columns of the index must be used with the = or IN or IS operators. The right-most column that is used can employ inequalities.

You may have to uninstall the app from the device so that the db is deleted and rerun to recreate it, or increase the version number of the db so that you can create the index in the onUpgrade() method.

user_104988280
Related