On 1 Oct 2016, at 1:22pm, Heather, James (ELS-LON) <james.heat...@elsevier.com> wrote:
> 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? The six individual indexes will speed up the SELECT a bit. But they're not the most efficient way of doing the job and, looking at your SELECT, I'd guess that only one of your six would get used and the others are just wasting space in the database file. The two indexes I supplied will speed up the SELECT far more, though again only one will get used. Try timings in another SQL engine if you want. There's a type of SQL newbie who creates individual indexes for each column of each table. It's a reasonable thing to do if you've just found out indexes speed things up but don't understand why. After a while they learn to do it properly: creating indexes perfectly suited to each WHERE/ORDER BY clause. And by coincidence my own real (non-Western) name also does not work properly with a middle initial. So you got two answers to your question, both from people whose names don't fit in the firstname-middleinitial-surname system. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
