On 1 Feb 2014, at 4:21pm, Stephen Chrzanowski <pontia...@gmail.com> wrote:
> What was the raw results of some of the queries? Obviously query and > explain, but did you also tag in time deltas pre and post queries? > > I'll have to read up more on EXPLAIN. I'm aware of how indexes work as > well as where and when you need to put them in, but, when I had my SQL > training back on MS SQL 2000, back then the engine was knowledgeable to use > multiple indexes versus SQLite which uses one. More useful than EXPLAIN for this purpose is EXPLAIN QUERY PLAN. Also make sure you read up on (and use) ANALYZE. ANALYZE does an equivalent analysis job to MS SQL's Tuning Wizard, though the results are stored for later use by SQLite rather than being presented to the user. > Would be interesting to see > when and where that single index comes into play when multiple indexes are > defined. MS SQL allows for many sorts of indexes, and SQLite has only one. MS SQL has a Tuning Wizard, which will recommend indexes to create or drop, but SQLite has a better query optimiser which is better at figuring out which index (per table) to use, partly because it can use the results of ANALYZE. My understanding of the use of multiple indexes for one query is that MS SQL can combine indexes (doing some sort of JOIN operation), but if someone can point me at a discussion of it I'll be grateful. They're the results of two different approaches to the problems. MS SQL runs on big computers which can sit in a nice cool server room; SQLite runs on user-machines and tiny handheld devices like mobile phones. MS SQL throws hardware and manpower at the problem: big files, big memory footprint, lots of processing, use all the power you want, provide training courses so people can become experts on your many complicated features. SQLite tries to make everything as simple as possible: precompute where possible, make choices simple and allow fewer of them, stay small and nimble, work well even when the programmer isn't an expert on your technology. They work differently because their functions are different. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
