Re: [sqlite] Confused about Multiple Indexes

Hi Max Thanks for the reply. On 26/11/2010 7:11 PM, Max Vlasov wrote: > Mohit said that he uses a someone's db, so I can imagine a possibility that > with two indexes ... > CREATE INDEX IDX1 on tx(name ASC); > CREATE INDEX IDX2 on tx(type, name ASC); > ... the creator of database wanted to

Re: [sqlite] Confused about Multiple Indexes

On 26/11/2010 6:34 PM, Swithun Crowe wrote: > Hello > > CREATE INDEX idx ON tx(name ASC, type); > > With the columns in this order (name followed by type), the index will be > used for queries which have either just name, or both name and type in > their WHERE clauses. Swithun, thank you very

Re: [sqlite] Confused about Multiple Indexes

On Fri, Nov 26, 2010 at 1:34 PM, Swithun Crowe < swit...@swithun.servebeer.com> wrote: > Hello > > MS> The second index should be: > MS> CREATE INDEX IDX2 on tx(type, name ASC); > > MS> What I had meant to ask was whether there is any benefit in having two > MS> indexes when one of the indexes is

Re: [sqlite] Confused about Multiple Indexes

Hello MS> The second index should be: MS> CREATE INDEX IDX2 on tx(type, name ASC); MS> What I had meant to ask was whether there is any benefit in having two MS> indexes when one of the indexes is exactly within the other. MS> IDX1 is index on 'name ASC' while IDX2 is an index on 'type, name

Re: [sqlite] Confused about Multiple Indexes

Hi Swithun Thank you for your reply. I'm sorry I was simplifying the schema when I sent it out. On 26/11/2010 5:35 PM, Swithun Crowe wrote: > Hello > > MS> CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, > MS> ...); > > MS> CREATE INDEX IDX1 on tx(name ASC); > MS>

Re: [sqlite] Confused about Multiple Indexes

Hello MS> CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, MS> ...); MS> CREATE INDEX IDX1 on tx(name ASC); MS> CREATE INDEX IDX2 on tx(type, search_name ASC); The two indexes cover different columns, so they do different things. The indexes you need depend on the

[sqlite] Confused about Multiple Indexes

Hi All, I am looking at a database that someone created earlier and for one of the table, the schema shows: CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, ...); CREATE INDEX IDX1 on tx(name ASC); CREATE INDEX IDX2 on tx(type, search_name ASC); I am under the impression