How to speed up query with multiple INNER JOINs

As requested, I'm reposting my previous comment as an actual answer (when I first posted the comment I was not able, for some reason, to post it as an answer):

MS Access is very aggressive about indexing columns on your behalf, whereas SQLite will require you to explicitly create the indexes you need. So, it's possible that Access has indexed either [Description] or [D] for you but that those indexes are missing in SQLite. I don't have experience with that amount of JOIN activity in SQLite. I used it in one Django project with a relatively small amount of data and did not detect any performance issues.

Do you have issues with referencial integrity? I ask because have the impression you've got unnecessary joins, so I re-wrote your query as:

SELECT DISTINCT        t.[oCode] AS OptionCode,        t.[descShort] AS OptionDescription   FROM DESCRIPTIONS t  JOIN tbl_D_H h ON h.[D] = t.[oCode]                AND h.[oType] = 'STANDARD'  JOIN tbl_D_J j ON j.[D] = t.[oCode]                AND j.[oType] = 'STANDARD'  JOIN tbl_Y_D d ON d.[D] = t.[oCode]                AND d.[Y] = '41'                AND d.[oType] ='STANDARD' WHERE t.[oMod] = 'D'

If DESCRIPTIONS and tbl_D_E have multiple row scans then oCode and D should be indexed. Look at example here to see how to index and tell how many row scans there are (http://www.siteconsortium.com/h/p1.php?id=mysql002).

This might fix it though ..

CREATE INDEX ocode_index ON DESCRIPTIONS (oCode) USING BTREE;CREATE INDEX d_index ON tbl_D_E (D) USING BTREE;

etc ....

Indexing correctly is one piece of the puzzle that can easily double, triple or more the speed of the query.