On 17 Jun 2010, at 2:03pm, Gregoire de Turckheim wrote: > This might not be the scope of sqlite.org documentation area, but it > could be a good idea to have a paper about how indexes work.
Yeah, I really have to write that sometime, don't I ? On 17 Jun 2010, at 2:44pm, Black, Michael (IS) wrote: > It seems to me that if you do "SELECT * FROM log WHERE id_clie...@idclient > ORDER BY utc DESC LIMIT 1" aren't you better off with two indexes, one for > id_client and one for utc? That way you don't have to worry about order > dependence at all. It seems only if you start doing WHERE clauses on both > should you need a compound index. There is an ideal index for every SELECT. You start with the fields in the WHERE, then the fields in the ORDER BY clause, then because of how SQLite works you might want to include the ones you're reporting. CREATE INDEX client_order ON log (id_client, utc DESC) would theoretically be the 'perfect' index for that SELECT statement. > And in this specific case unless you have a boat load of utc's for each > client_id the utc index isn't going to buy you much at all. True. It's all about clumpiness and subclumpiness. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
