On Sun, Jul 18, 2010 at 04:31:20PM +0100, Simon Slavin scratched on the wall: > On 18 Jul 2010, at 2:56pm, Peng Yu wrote:
> > So, in general, what the index I should use depends on what the select > > statement I might use? > Exactly. There is one index which is ideal for each SELECT statement. With simple queries, you can often make a pretty good guess, but in the general case this is definitely not true. Obviously a SELECT statement can use multiple indexes, and that set might change depending on which other indexes are (or are not) available. For a given SELECT statement, the "ideal" query plan (and, therefore, the indexes it uses) is also heavily dependent on the data currently held in the database. That's why the ANALYZE command exists. Just adding or removing data from the tables, without altering the table definition or the SELECT statement, can change the "ideal" plan. > There is no point in arbitrarily creating separate indices for every > column: most of them will never be used and the ones which aren't used > won't speed things up much. And, in fact, will slow things down. Every INSERT/UPDATE/DELETE statement that is run on a table must also update all of the table's indexes. Each index adds a non-trivial amount of overhead. > > select * from test group by value1,value2; > > select * from test group by value1||value2; Be aware that these statements do not produce the same answer. If you have a set of rows like this: 'abcd', 'ef' 'abc', 'def' The first statement will consider these different, while the second statement will group them together. In most cases, you really want the first (for example, first_name, last_name). As Simon pointed out, the first one can also utilize an index, while the second one cannot. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
