SQLite index benchmark

The problem

I have the following schema in an SQLite DB:

table: Dispatches

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

The most common query is:

SELECT * FROM dispatches WHERE route = 1 ORDER BY slot ASC

While attempting to optimize the indexes used I tried 4 approaches:

1. No indexes
2. Index by route
3. Composite index by route and slot
4. Separate index by route and slot

For each case I'm inserting n=10000 records. The route is either 0 or 1 (random) and the slot is a random number between 0 and n-1.

Shockingly, when results are ran and the approaches are ordered by speed, I'm getting the same order. In other words, the fastest one is the table with no indexes.

SQLite details

Here are the EXPLAIN PLAN outputs (edited):

1. SCAN TABLE dispatches USE TEMP B-TREE FOR ORDER BY
2. SEARCH TABLE dispatch_index_routes USING INDEX index_dispatch_index_routes_on_route (route=?) USE TEMP B-TREE FOR 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 FOR ORDER BY

Test script

This was all implemented using ActiveRecord (I'm actually optimizing for an Android app, but I figured that it shouldn't be that much different when benchmarking in a PC):

#!/usr/bin/ruby

require 'active_record'
require 'sqlite3'
require 'benchmark'

db_name = 'test_db'

ActiveRecord::Base.establish_connection(
  adapter:  'sqlite3', # or 'postgresql' or 'sqlite3'
  host:     'localhost',
  database: db_name
)

class Dispatch < ActiveRecord::Base

end

class DispatchIndexRoute < ActiveRecord::Base

end

class DispatchIndexComposite < ActiveRecord::Base

end

class DispatchIndexSeparate < ActiveRecord::Base

end

# Define a minimal database schema
unless ActiveRecord::Base.connection.table_exists?(:dispatches)
  ActiveRecord::Base.connection.create_table :dispatches, force: true do |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 do |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 do |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 do |t|
    t.string  :identifier
    t.integer :route
    t.integer :slot

    t.index [:route, :slot]
  end
end

table_names = [Dispatch.table_name, DispatchIndexRoute.table_name, DispatchIndexComposite.table_name, DispatchIndexSeparate.table_name]

table_names.each do |table_name|
  ActiveRecord::Base.connection.execute("DELETE FROM #{table_name};")
  ActiveRecord::Base.connection.execute("VACUUM;")
end

puts "Creating items"
n = 50000
n.times.each do |i|
  name = SecureRandom.hex
  route = Random.rand(2)
  slot = Random.rand(n)
  Dispatch.new(identifier: name, route: route, slot: slot).save!
  DispatchIndexRoute.new(identifier: name, route: route, slot: slot).save!
  DispatchIndexComposite.new(identifier: name, route: route, slot: slot).save!
  DispatchIndexSeparate.new(identifier: name, route: route, slot: slot).save!
  if i > 0 && (i % (n / 10)) == 0
    print i
  end
end
puts "Done creating items"

iterations = 1
table_names.each do |table_name|
  puts "Results for #{table_name}"
  query = "SELECT * FROM #{table_name} WHERE route = 1 ORDER BY slot ASC"
  puts ActiveRecord::Base.connection.execute("EXPLAIN QUERY PLAN #{query}")
  Benchmark.bm do |bm|
    bm.report do
      iterations.times do
        ActiveRecord::Base.connection.execute(query)
      end
    end
  end
end

And the output:

Creating items
100020003000400050006000700080009000Done creating items
Results for dispatches
       user     system      total        real
   0.100000   0.010000   0.110000 (  0.108072)
Results for dispatch_index_routes
       user     system      total        real
   0.120000   0.000000   0.120000 (  0.123919)
Results for dispatch_index_composites
       user     system      total        real
   0.130000   0.000000   0.130000 (  0.138045)
Results for 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 the results. Shouldn't the composite index be the fastest? I see that the first one is using SCAN instead o SEARCH, but still. How can it be faster?