On 28 Sep 2016, at 8:01pm, Heather, James (ELS-LON) <james.heat...@elsevier.com> wrote:
> But if I add indexes to the three columns on each table (six indexes in all): > > CREATE INDEX `idx_uk_givenname` ON `fakenames_uk` (`givenname` ) > //etc. > > then it runs painfully slowly: I can't solve the mystery, but I can help you do your task properly. The indexes you created hardly help the query at all. They allow SQL to speed up searching on just one of the variables. The indexes you really want are CREATE INDEX `idx_uk_sgm` ON `fakenames_uk` (`surname`,`givenname`,`middleinitial`) CREATE INDEX `idx_usa_sgm` ON `fakenames_usa` (`surname`,`givenname`,`middleinitial`) Once you have those indexed created, and have data in the tables, perform this command ANALYZE You only need to do it once because the results are stored in the database and consulted when needed. That should let SQLite perform your SELECT as fast as it can. You might try getting timings before and after the ANALYZE. By the way, if you're going to do lots in SQLite, try to lose the backticks. That's a non-standard thing that MySQL uses to delimit entity names. You don't need to use anything at all in SQLite since all strings are quoted with single quotes and there are no ambiguous cases. However this is nothing to do with your question and will not change any timings much. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
