SQLite Indexes causing query performance degredation

Question

I'm busy optimizing a SQLite database for 1 large query by testing the results of adding indexes to tables in the query.

The first thing I noticed is that going index beserk degraded performance, so I have since removed all my indexes and shrunk the database and started testing the performance of applying 1 index at a time.

Doing this I discovered one index which speeds up the query by about 50%. I then applied this index to a another SQLite database (same structure just more data) with about 5x the amount of data (500MB) and now I find that the same index degrades performance in the larger DB by about 35%.

So I have done some reading into PRAGMA cache_size and I read that if your table is large and you apply indexes to it, that the cache would quickly be used up by the indexes and this would degrade performance. So maybe changing this size would help...

So my question is, why do indexes degrade performance in a database with a larger sample data set than with the database with a smaller data set ?

More Information:

My query is a select query only and the database is only read from, never written to (except obviously the first time it got populated). The index i applied was on an a single integer column.

Query:

select distinct

u.userName 'User Name',
u.description 'User Description',
ma.abilityName 'Ability',
ifnull(a.applicationName, '') 'Module',
ifnull(rr3.roleName, '') 'Parent Role',
r.responsibilityName 'Responsibility',
r.description 'Responsibility Description',
ff.functionName 'Technical Function Name',
ifnull(ff.userFunctionName, '') 'Function Name',
ff.description 'Function Description',
ff.type 'Function Type',             
ifnull(f.formName, '') 'Form',
ifnull(f.userFormName, '') 'Form Name',             
case ifnull(me.isProcessTab, 0) when 0 then 'N' else 'Y' end as 'Is From Process Tab?',
m.menuName 'Menu Name',
m.menuDisplayName 'Menu Description',
ifnull(sg.securityGroupName, '') 'Data Group Name',
ifnull(rg.requestGroupName, '') 'Request Group Name',
mpath.path as 'Full Menu Path',              
cast(ma.ID as nvarchar) || ':' || cast(u.ID as nvarchar) as 'Ledger Mapping'

from matrix_processes  mp   
inner join matrix_processAbilities  mpa on mpa.processID = mp.ID
inner join matrix_abilities ma on ma.ID = mpa.abilityID
inner join matrix_abilityFunctions maf on maf.abilityID = ma.ID
inner join matrix_functions mf on mf.ID = maf.functionID  --mf.functionName = ff.functionName
inner join formFunctions  ff on ff.functionName = mf.functionName
inner join users_functions uf on uf.functionID = ff.ID
inner join users_responsibilities as ur on ur.userID = uf.userID           
inner join responsibilities_menus_hierarchy_functions  rmhf on rmhf.responsibilityID = ur.responsibilityID and rmhf.functionID = ff.ID         

inner join users as u on u.ID = uf.userID
inner join responsibilities as r on r.ID = ur.responsibilityID

left join requestGroups as rg on rg.ID = r.requestGroupID
left join securityGroups as sg on sg.securityGroupKey = r.dataGroupKey

inner join menus_hierarchy  mh on mh.ID = rmhf.menuHierarchyID
inner join menus  as m on m.id = mh.menuParentID
inner join menuPaths mpath  on mpath.ID = mh.menuPathID
left join menuEntries me on me.menuID = m.ID

left join applications as a on a.ID = r.applicationID
left join forms  as f on f.ID = ff.formID

left join roles_responsibilities rr1 on rr1.responsibilityID = r.ID
left join roles_roles rr2 on rr2.roleID = rr1.roleID
left join roles rr3 on rr3.ID = rr2.roleParentID

where mp.ID = 1033

order by u.userName,
      ma.abilityName,
      r.responsibilityName,
      ff.userFunctionName;

Query Plan:

0   0   0   SCAN TABLE matrix_processes AS mp (~100000 rows)
0   1   1   SEARCH TABLE matrix_processAbilities AS mpa USING AUTOMATIC COVERING INDEX (processID=?) (~7 rows)
0   2   2   SEARCH TABLE matrix_abilities AS ma USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   3   3   SEARCH TABLE matrix_abilityFunctions AS maf USING AUTOMATIC COVERING INDEX (abilityID=?) (~7 rows)
0   4   4   SEARCH TABLE matrix_functions AS mf USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   5   5   SEARCH TABLE formFunctions AS ff USING AUTOMATIC COVERING INDEX (functionName=?) (~7 rows)
0   6   6   SEARCH TABLE users_functions AS uf USING INDEX IX_UserAccessDetailsOpt_users_functions2 (functionID=?) (~10 rows)
0   7   7   SEARCH TABLE users_responsibilities AS ur USING AUTOMATIC COVERING INDEX (userID=?) (~7 rows)
0   8   8   SEARCH TABLE responsibilities_menus_hierarchy_functions AS rmhf USING AUTOMATIC COVERING INDEX (responsibilityID=? AND functionID=?) (~7 rows)
0   9   9   SEARCH TABLE users AS u USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   10  10  SEARCH TABLE responsibilities AS r USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   11  11  SEARCH TABLE requestGroups AS rg USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   12  12  SEARCH TABLE securityGroups AS sg USING AUTOMATIC COVERING INDEX (securityGroupKey=?) (~7 rows)
0   13  13  SEARCH TABLE menus_hierarchy AS mh USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   14  14  SEARCH TABLE menus AS m USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   15  15  SEARCH TABLE menuPaths AS mpath USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   16  16  SEARCH TABLE menuEntries AS me USING AUTOMATIC COVERING INDEX (menuID=?) (~7 rows)
0   17  17  SEARCH TABLE applications AS a USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   18  18  SEARCH TABLE forms AS f USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   19  19  SEARCH TABLE roles_responsibilities AS rr1 USING AUTOMATIC COVERING INDEX (responsibilityID=?) (~7 rows)
0   20  20  SEARCH TABLE roles_roles AS rr2 USING AUTOMATIC COVERING INDEX (roleID=?) (~7 rows)
0   21  21  SEARCH TABLE roles AS rr3 USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   0   0   USE TEMP B-TREE FOR DISTINCT
0   0   0   USE TEMP B-TREE FOR ORDER BY

Answer 1

AUTOMATIC INDEX means that SQLite thinks it is worthwhile to create a temporary index (even with the time needed to collect all table data and sort it for the index). The time needed for writing and sorting all data is in O(n log n).

You should be able to speed up this query by adding all these indexes to the database permanently:

CREATE INDEX mpa_processID ON matrix_processAbilities(processID);
CREATE INDEX ma_ID ON matrix_abilities(ID);
...

Furthermore, the lookup where mp.ID = 1033 can be sped up with an index on this column. (The optimizer decided that creating a temporary index for this would not be worth it because this table is searched only once.)

Please note that any ID column that is a primary key and has an integer type should be declared as INTEGER PRIMARY KEY in the table definition instead, which is a little bit more efficient than a separate index.