1

I have a SQLite table created in a manner something like this:

  CREATE TABLE ex2(_ID INTEGER PRIMARY KEY AUTOINCREMENT,x REAL,y REAL,z TEXT);

My query against this table, will always be:

SELECT z FROM ex2 WHERE x BETWEEN 1.5 AND 100.5 AND y BETWEEN 10.2 AND 65.3;

My query asks for results satisfying a range constraints for the x and y column values (REAL data type). Also just to clarify, 1.5,100.5, 10.2 and 65.3 are not fixed. Just to illustrate a sample query.

x and y column values are NOT going to be unique for sure.

What is the best way to create the index(es)? My options (and testing so far):

Option 1:

  CREATE INDEX ex2i1 ON ex2(x);
  CREATE INDEX ex2i2 ON ex2(y);

Options 2:

  CREATE INDEX ex2i12 ON ex2(x,y);

I have done some (crude) performance tests on my Android test device, and I can't seem to find a concrete proof on which index is actually better.

alpinescrambler
  • 1,934
  • 16
  • 26
  • 2
    Use [EXPLAIN QUERY PLAN](http://www.sqlite.org/eqp.html) do check which indexes are being used. Anyway, multidimensial range queries are better done with [R-trees](http://www.sqlite.org/rtree.html). – CL. Nov 28 '14 at 21:07
  • Unfortunately, R-tree module doesn't look like it's included in the standard Android build. I may go into that direction if it is really necessary. – alpinescrambler Nov 28 '14 at 21:26

1 Answers1

0

Option 2 (i.e. composite index) should be better in general. I think this is a good article on this topic.

Updated
Here is also good post

P.S. By the way, to see the difference you have to create really big table (I would say not less than 10 000 records) with random values and select several ranges of x and y

Community
  • 1
  • 1
sberezin
  • 3,266
  • 23
  • 28