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. Thanks very much for this. This is odd. I had rather assumed it would be possible to speed up the searching using those individual indexes... in fact I know it's possible, because MySQL performs the same query very much faster when I add the individual indexes. How MySQL differs from sqlite in terms of its optimiser, I've no idea; but am I right that MySQL stands as an existence proof that the individual indexes can be used to solve the problem efficiently? 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". Right. In fact I didn't use any quoting when I created the indexes. When I grabbed the index creation statements for pasting into the email, I got sqlite-browser to give me the statements. I'm surprised it used backticks. I don't know whether that's something sqlite-browser has done for itself or not. (I did try all the queries from the command line, though, so I know that the main issue I've raised isn't a sqlite-browser one.) (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. :-) Also true, but for my application, it didn't matter. I just wanted to demonstrate to some people in a tutorial that the queries would run much faster if the columns were indexed, so I grabbed some fake names etc. from a web site that generates them, and stuffed them into a database. The column names were generated by the web site. It's not used for anything real. James ________________________________ Elsevier Limited. Registered Office: The Boulevard, Langford Lane, Kidlington, Oxford, OX5 1GB, United Kingdom, Registration No. 1982084, Registered in England and Wales. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
