How to optimize a large join to create a WHERE IN
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