Re: [sqlite] SELECT behaviour with INDEX

On Thu, Jun 17, 2010 at 09:07:56AM -0500, Black, Michael (IS) scratched on the wall: > I sense the beginning of a "How to use indexes" page. > > Things you need know: > Only one index is used per query (or insert??). It isn't that bad. In general, it is "one index PER TABLE, per

Re: [sqlite] SELECT behaviour with INDEX

On Thu, Jun 17, 2010 at 08:44:25AM -0500, Black, Michael (IS) scratched on the wall: > Is there any advantage/disadvantage to having seperate indexes in a > case like this? In a case like this, no. If each column was indexed individually one one of them would be used in this query. There

Re: [sqlite] SELECT behaviour with INDEX

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)

Re: [sqlite] SELECT behaviour with INDEX

rthrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov Sent: Thu 6/17/2010 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SELECT behaviour with INDEX > Is there any advantage/disadvantage to

Re: [sqlite] SELECT behaviour with INDEX

> > > > From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov > Sent: Thu 6/17/2010 7:40 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SELECT behaviour with INDEX > > > >> It s

Re: [sqlite] SELECT behaviour with INDEX

Discussion of SQLite Database Subject: Re: [sqlite] SELECT behaviour with INDEX > It seems to me the DB engine tries to perform an unindexed search if the > requested value is not present in the index. > Is this behaviour intended ? How can I get rid of it ? If your index is in that ver

Re: [sqlite] SELECT behaviour with INDEX

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. It's simple, really. Imagine a phonebook, with names sorted alphabetically. Consider each letter of the name

Re: [sqlite] SELECT behaviour with INDEX

Pavel Ivanov a écrit : >> It seems to me the DB engine tries to perform an unindexed search if the >> requested value is not present in the index. >> Is this behaviour intended ? How can I get rid of it ? >> > > If your index is in that very order as you said, i.e. (utc, > id_client), then

Re: [sqlite] SELECT behaviour with INDEX

> It seems to me the DB engine tries to perform an unindexed search if the > requested value is not present in the index. > Is this behaviour intended ? How can I get rid of it ? If your index is in that very order as you said, i.e. (utc, id_client), then whatever @IdClient you put in the query

Re: [sqlite] SELECT behaviour with INDEX

Gregoire de Turckheim wrote: > I'm noticing a strange behaviour with SELECT statements. > > Let's consider this statement : > > SELECT * FROM log > WHERE id_clie...@idclient > ORDER BY utc DESC > LIMIT 1 > > The table has an index on the "utc" and "id_client" fields

[sqlite] SELECT behaviour with INDEX

Hi, I'm noticing a strange behaviour with SELECT statements. Let's consider this statement : SELECT * FROM log WHERE id_clie...@idclient ORDER BY utc DESC LIMIT 1 The table has an index on the "utc" and "id_client" fields pair. When running this query with an @IdClient value which exists in