How to optimize a large join to create a WHERE IN The following sqlite database query takes about 40 seconds on my Core i7.

SELECT t.a
FROM titles t
WHERE t.a IN (
    SELECT e.id 
    FROM erpubtbl e
    JOIN abstract_mesh am ON am.pmid = e.pmid
    JOIN mesh_descriptor md ON md.id = am.mesh_id
    WHERE md.id = 100
    ORDER BY e.journal_year DESC)
AND t MATCH 'test';


I'm pretty sure the slow part is the IN.  The abstract_mesh table is a million plus rows.  Any idea how to speed this up, or suggestions for how to alter the schema to be more efficient?  

Here's the schema:

CREATE TABLE `abstract_mesh` (
  `mesh_id` integer NOT NULL
,  `major_topic` varchar(1) DEFAULT NULL
,  `pmid` integer DEFAULT NULL
);

CREATE TABLE `erpubtbl` (
  `id` integer NOT NULL
,  `journal_id` integer  NOT NULL DEFAULT '0'
,  `pmid` integer NOT NULL DEFAULT '0'
,  `title` text
,  `author` varchar(50) DEFAULT NULL
,  `journal_month` varchar(25) DEFAULT NULL
,  `journal_year` integer DEFAULT NULL
,  `journal_page` varchar(50) DEFAULT NULL
,  `volume` varchar(100) DEFAULT NULL
,  `issue` varchar(50) DEFAULT NULL
,  `pubmed_entry_year` varchar(50) DEFAULT NULL
,  `pubmed_entry_month` varchar(50) DEFAULT NULL
,  `medline_pgn` varchar(50) DEFAULT NULL
,  `no_abstract` integer DEFAULT NULL
,  `publication_type` varchar(250) DEFAULT NULL
,  `country` varchar(50) DEFAULT NULL
,  `citation_subset` varchar(50) DEFAULT NULL
,  PRIMARY KEY (`id`,`journal_id`,`pmid`)
,  UNIQUE (`id`,`pmid`)
);

CREATE TABLE `mesh_descriptor` (
  `id` integer  NOT NULL PRIMARY KEY AUTOINCREMENT
,  `name` varchar(250) DEFAULT NULL
,  UNIQUE (`id`)
,  UNIQUE (`name`)
);

CREATE INDEX mesh_descriptor_name_idx on mesh_descriptor (name);
CREATE INDEX erpubtbl_year_idx on erpubtbl (journal_year);
CREATE VIRTUAL TABLE titles USING fts4(a NUMBER, t TEXT, tokenize=porter);
The abstract_mesh table has no index.
As this EXPLAIN QUERY PLAN output (from SQLite 3.8.10.2) shows, searching in this table is so slow that it is worthwhile to create a temporay index as part of the execution of the query:

0|0|0|SCAN TABLE titles AS t VIRTUAL TABLE INDEX 3:
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|2|SEARCH TABLE mesh_descriptor AS md USING INTEGER PRIMARY KEY (rowid=?)
1|1|0|SCAN TABLE erpubtbl AS e USING INDEX erpubtbl_year_idx
1|2|1|SEARCH TABLE abstract_mesh AS am USING AUTOMATIC COVERING INDEX (pmid=? AND mesh_id=?)


You should create such an index explicitly so that the query does not need to:

CREATE INDEX abstract_mesh_pmid_mesh_id_idx ON abstract_mesh(pmid, mesh_id);


However, if you create some other indexes:

CREATE INDEX abstract_mesh_meshid_pmid ON abstract_mesh(mesh_id, pmid);
CREATE INDEX e_pmid ON erpubtbl(pmid);


this allows the database to use a different execution plan, which might or might not be faster for your data:

0|0|0|SCAN TABLE titles AS t VIRTUAL TABLE INDEX 3:
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|2|SEARCH TABLE mesh_descriptor AS md USING INTEGER PRIMARY KEY (rowid=?)
1|1|1|SEARCH TABLE abstract_mesh AS am USING COVERING INDEX abstract_mesh_meshid_pmid (mesh_id=?)
1|2|0|SEARCH TABLE erpubtbl AS e USING INDEX e_pmid (pmid=?)
1|0|0|USE TEMP B-TREE FOR ORDER BY