I have a sqlite database with two tables, each with 50,000 rows in, containing names of (fake) people. I've constructed a simple query to find out how many names there are (given name, middle initial, surname) that are common to both tables:
select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;
When there are no indexes except on the primary keys (irrelevant to this query), it runs quickly:
[james@marlon Downloads] $ time sqlite3 generic_data_no_indexes.sqlite "select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;"
131
real 0m0.115s
user 0m0.111s
sys 0m0.004s
But if I add indexes to the three columns on each table (six indexes in all):
CREATE INDEX `idx_uk_givenname` ON `fakenames_uk` (`givenname` )
//etc.
then it runs painfully slowly:
[james@marlon Downloads] $ time sqlite3 generic_data.sqlite "select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;"
131
real 1m43.102s
user 0m52.397s
sys 0m50.696s
Is there any rhyme or reason to this?
Here's the result of EXPLAIN QUERY PLAN
for the version without indexes:
0|0|0|SCAN TABLE fakenames_uk
0|1|1|SEARCH TABLE fakenames_usa USING AUTOMATIC COVERING INDEX (middleinitial=? AND surname=? AND givenname=?)
This is with indexes:
0|0|0|SCAN TABLE fakenames_uk
0|1|1|SEARCH TABLE fakenames_usa USING INDEX idx_us_middleinitial (middleinitial=?)
middleinitial
,surname
andgivenname
)?SELECT c FROM t WHERE a=1 AND b=2
, the indext(a,b,c)
is covering butt(a,b)
is not. The benefit of covering indices is that the whole query result can be pulled directly out of the index, whereas non-covering indices quickly find the relevant rows but it still needs to refer to the main table data to pick out the values.