[Solved] SQLite Indexes causing query performance degredation

How to optimize this SQL query?

In case you have your own slow SQL query, you can optimize it automatically here.

For the query above, the following recommendations will be helpful as part of the SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid using too many joins (modified query below): Using too many joins can complicate the query's readability and the ability of the database optimizer to optimize the query.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `applications` ADD INDEX `applications_idx_id` (`ID`);
ALTER TABLE `formFunctions` ADD INDEX `formfunctions_idx_functionname` (`functionName`);
ALTER TABLE `forms` ADD INDEX `forms_idx_id` (`ID`);
ALTER TABLE `matrix_abilities` ADD INDEX `matrix_abilities_idx_id` (`ID`);
ALTER TABLE `matrix_abilityFunctions` ADD INDEX `matrix_abilityfunc_idx_abilityid` (`abilityID`);
ALTER TABLE `matrix_functions` ADD INDEX `matrix_functions_idx_id` (`ID`);
ALTER TABLE `matrix_processAbilities` ADD INDEX `matrix_processabil_idx_processid` (`processID`);
ALTER TABLE `matrix_processes` ADD INDEX `matrix_processes_idx_id` (`ID`);
ALTER TABLE `menuEntries` ADD INDEX `menuentries_idx_menuid` (`menuID`);
ALTER TABLE `menuPaths` ADD INDEX `menupaths_idx_id` (`ID`);
ALTER TABLE `menus` ADD INDEX `menus_idx_id` (`id`);
ALTER TABLE `menus_hierarchy` ADD INDEX `menus_hierarchy_idx_id` (`ID`);
ALTER TABLE `requestGroups` ADD INDEX `requestgroups_idx_id` (`ID`);
ALTER TABLE `responsibilities` ADD INDEX `responsibilities_idx_id` (`ID`);
ALTER TABLE `responsibilities_menus_hierarchy_functions` ADD INDEX `responsibilities_h_idx_responsibilityid` (`responsibilityID`);
ALTER TABLE `roles` ADD INDEX `roles_idx_id` (`ID`);
ALTER TABLE `roles_responsibilities` ADD INDEX `roles_responsibili_idx_responsibilityid` (`responsibilityID`);
ALTER TABLE `roles_roles` ADD INDEX `roles_roles_idx_roleid` (`roleID`);
ALTER TABLE `securityGroups` ADD INDEX `securitygroups_idx_securitygroupkey` (`securityGroupKey`);
ALTER TABLE `users` ADD INDEX `users_idx_id` (`ID`);
ALTER TABLE `users_functions` ADD INDEX `users_functions_idx_functionid` (`functionID`);
ALTER TABLE `users_responsibilities` ADD INDEX `users_responsibili_idx_userid` (`userID`);
The optimized 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) 
OR ':' 
OR 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 
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

Related Articles



* original question posted on StackOverflow here.