SQL Query very slow

[nilamo]

Places.Name like ElectionResults2012.Town + '%'

Is the name the only way the two tables are related? There's no ids or anything? I think that, by default, sqlite can't use indices for 'like' queries due to case sensitivity. You can check what the sql engine is doing with your query by asking it to explain query: https://www.sqlite.org/eqp.html

Depending on how much control you have, rebuilding the table to use a fulltext search engine would make it work as expected, if you use the keyword 'match' instead of 'like': https://www.sqlite.org/fts3.html

Or, you can use pragma declarations to let sql know you don't care about case sensitivity, then it might decide to use the index: https://www.sqlite.org/optoverview.html#...timization
https://stackoverflow.com/a/8586390

sqlite apparently doesn't let you alter tables that already exist, so you can't modify the collation of a preexisting table. But you can create a temporary table to hold the contents, drop the table, recreate it using the proper collation, then put the data back in:https://stackoverflow.com/a/47469890

CREATE TABLE table01 (id, name countrycode, comment );
INSERT INTO table01 SELECT id, name, countrycode, comment FROM table;
DROP TABLE table;
CREATE TABLE table (
   id       integer PRIMARY KEY AUTOINCREMENT,
   name     text COLLATE NOCASE
   country  integer,
   comment  text COLLATE NOCASE
);
INSERT INTO table (id, name, countrycode, comment)
    SELECT id, name, countrycode, comment FROM table01;
DROP TABLE table01;