On 9/28/16, Heather, James (ELS-LON) <james.heat...@elsevier.com> wrote: > I have a sqlite database with two tables, each with 50,000 rows in, > containing names of (fake) people. I've constructed a simple query to find > out how many names there are (given name, middle initial, surname) that are > common to both tables: > > When there are no indexes except on the primary keys (irrelevant to this > query), it runs quickly: > > But if I add indexes to the three columns on each table (six indexes in > all): > > then it runs painfully slowly: > > Is there any rhyme or reason to this? I'm running 3.13.0, but I've also > tried with 3.11.0, and got the same problem. > > Here's the query plan for the version with the indexes: >
Without any indexes, SQLite is probably creating its own transient index to use for the join. And when SQLite creates an index, it will be creating a single index on all three columns being joined. But if you have your own (inferior, single-column) indexes, then SQLite figures you know what you are doing and so it tries to use your indexes rather than create its own. But the single-column indexes are far less useful at solving the problem. Rather than creating 6 new indexes, I suggest creating just one index: CREATE INDEX gofast ON fakenames_uk (givenname, middleinitial, surname); If that doesn't work. Please run ANALYZE, then send in the output of ".fullschema" for your database and I'll look again. Unrelated stylistic notes: (1) the grave accent quotes (`givenname`) is a MySQL-ism. SQLite understands this for compatibility, but it is not standard SQL. No systems other than MySQL and SQLite understand it. Better to use double-quotes: "givenname". (2) There exist many people in the world whose names do not parse neatly into "givenname", "middleinitial", "surname". And those people tend to become grumpy when presented with a form that wants a name in that format. :-) -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
