sql - SQLite index benchmark -

the problem

i have following schema in sqlite db:

table: dispatches

• rowid: primary key
• identifier: string
• route: integer
• slot: integer

the common query is:

select * dispatches route = 1 order slot asc 

while attempting optimize indexes used tried 4 approaches:

1. no indexes
2. index route
3. composite index route , slot
4. separate index route , slot

for each case i'm inserting n=10000 records. route either 0 or 1 (random) , slot random number between 0 , n-1.

shockingly, when results ran , approaches ordered speed, i'm getting same order. in other words, fastest 1 table no indexes.

sqlite details

here explain query plan outputs (edited):

1. scan table dispatches use temp b-tree order by
2. search table dispatch_index_routes using index index_dispatch_index_routes_on_route (route=?) use temp b-tree order by
3. search table dispatch_index_composites using index index_dispatch_index_composites_on_route_and_slot (route=?)
4. search table dispatch_index_separates using index index_dispatch_index_separates_on_route (route=?) use temp b-tree order by

test script

this implemented using activerecord (i'm optimizing android app, figured shouldn't different when benchmarking in pc):

#!/usr/bin/ruby  require 'active_record' require 'sqlite3' require 'benchmark'  db_name = 'test_db' # change following reflect database settings activerecord::base.establish_connection(   adapter:  'sqlite3', # or 'postgresql' or 'sqlite3'   host:     'localhost',   database: db_name )  #activerecord::base.connection.execute("create database if not exists #{db_name}") #activerecord::base.connection.execute("use test_db")  class dispatch < activerecord::base  end  class dispatchindexroute < activerecord::base  end  class dispatchindexcomposite < activerecord::base  end  class dispatchindexseparate < activerecord::base  end  class dispatchindexcovering < activerecord::base  end  # define minimal database schema unless activerecord::base.connection.table_exists?(:dispatches)   activerecord::base.connection.create_table :dispatches, force: true |t|     t.string  :identifier     t.integer :route     t.integer :slot   end end  unless activerecord::base.connection.table_exists?(:dispatch_index_routes)   activerecord::base.connection.create_table :dispatch_index_routes, force: true |t|     t.string  :identifier     t.integer :route, index: true     t.integer :slot   end end  unless activerecord::base.connection.table_exists?(:dispatch_index_separates)   activerecord::base.connection.create_table :dispatch_index_separates, force: true |t|     t.string  :identifier     t.integer :route, index: true     t.integer :slot, index: true   end end  unless activerecord::base.connection.table_exists?(:dispatch_index_composites)   activerecord::base.connection.create_table :dispatch_index_composites, force: true |t|     t.string  :identifier     t.integer :route     t.integer :slot      t.index [:route, :slot]   end end  unless activerecord::base.connection.table_exists?(:dispatch_index_coverings)   activerecord::base.connection.create_table :dispatch_index_coverings, force: true |t|     t.string  :identifier     t.integer :route     t.integer :slot      t.index [:route, :slot, :id, :identifier], name: 'dispatch_covering_idx'   end end  classes = [dispatch, dispatchindexroute, dispatchindexcomposite, dispatchindexseparate, dispatchindexcovering]  classes.each |c|   activerecord::base.connection.execute("delete #{c.table_name};")   activerecord::base.connection.execute("vacuum;") end  puts "creating items" n = 10000 n.times.each |i|   name = securerandom.hex   route = random.rand(2)   slot = random.rand(n)   classes.each |c|     c.new(identifier: name, route: route, slot: slot).save!   end   if > 0 && (i % (n / 10)) == 0     print   end end puts "done creating items"  activerecord::base.connection.execute("analyze;")  iterations = 1 classes.each |c|   table_name = c.table_name   puts "results #{table_name}"   query = "select * #{table_name} route = 1 order slot asc"   # puts activerecord::base.connection.execute("explain query plan #{query}")   benchmark.bm |bm|     bm.report       iterations.times         activerecord::base.connection.execute(query)       end     end   end end 

and output:

creating items 100020003000400050006000700080009000done creating items results dispatches        user     system      total        real    0.100000   0.010000   0.110000 (  0.108072) results dispatch_index_routes        user     system      total        real    0.120000   0.000000   0.120000 (  0.123919) results dispatch_index_composites        user     system      total        real    0.130000   0.000000   0.130000 (  0.138045) results dispatch_index_separates        user     system      total        real    0.140000   0.000000   0.140000 (  0.138734) 

versions:

$ sqlite3 --version 3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace $ ruby --version ruby 2.1.5p273 (2014-11-13 revision 48405) [x86_64-darwin14.0] 

question

i don't understand results. shouldn't composite index fastest? see first 1 using scan instead o search, still. how can faster?

edit

at first glance, adding covering index not speed queries:

results dispatches        user     system      total        real    0.100000   0.000000   0.100000 (  0.104801) results dispatch_index_routes        user     system      total        real    0.130000   0.010000   0.140000 (  0.133568) results dispatch_index_composites        user     system      total        real    0.130000   0.000000   0.130000 (  0.143194) results dispatch_index_separates        user     system      total        real    0.140000   0.000000   0.140000 (  0.144586) results dispatch_index_coverings        user     system      total        real    0.140000   0.000000   0.140000 (  0.137730) 

it better after running analyze @cl. suggested:

results dispatches        user     system      total        real    0.090000   0.000000   0.090000 (  0.098393) results dispatch_index_routes        user     system      total        real    0.130000   0.000000   0.130000 (  0.137758) results dispatch_index_composites        user     system      total        real    0.130000   0.010000   0.140000 (  0.134864) results dispatch_index_separates        user     system      total        real    0.140000   0.000000   0.140000 (  0.142418) results dispatch_index_coverings        user     system      total        real    0.100000   0.000000   0.100000 (  0.098227) 

trying less randomized route value doesn't much, since options speedup:

results dispatches        user     system      total        real    0.220000   0.010000   0.230000 (  0.228870) results dispatch_index_routes        user     system      total        real    0.170000   0.020000   0.190000 (  0.190722) results dispatch_index_composites        user     system      total        real    0.230000   0.000000   0.230000 (  0.242936) results dispatch_index_separates        user     system      total        real    0.240000   0.010000   0.250000 (  0.253333) results dispatch_index_coverings        user     system      total        real    0.190000   0.000000   0.190000 (  0.205272) 

the route either 0 or 1 (random)

i think key why performance same -- or indexes make things worse. if understand correctly, need read every other record.

a full-table scan reads records , discards unneeded ones. index scan goes index , reads ones need. however, bouncing between data pages each record. guess overhead of reading record in index , non-locality reference same reading additional row in table.

so, index filtering not going (actually, clustered index might help).

that leaves sorting. i'm not sure how sqlite optimizer works. however, following index might show gain: (route, slot, rowid, identifier). covering index, there no need go data pages when processing query.