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 search for a name regardless of the > type with a help of first index, and in the context of chosen type with the > second. So, Mohit, if some unknown queries or code involved I'd better leave > them as they are I have access to all the queries that are planned - I will confirm that the points Swithun mentioned are covered. If I remember correctly, he searches for all entries that have a certain kind of name, or all entries that are a certain type and have a certain kind of name. Best Regards, Mohit. 27/11/2010 | 12:41 AM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 much for the clear explanation. Best Regards, Mohit. 27/11/2010 | 12:35 AM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 exactly within the other. > > MS> IDX1 is index on 'name ASC' while IDX2 is an index on 'type, name ASC' > - > MS> does this mean that in a sense IDX1 is a subset of IDX2 and can be > removed? > > If you wanted to have just one index, rather than two, then you could > have: > > CREATE INDEX idx ON tx(name ASC, type); > > 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 search for a name regardless of the type with a help of first index, and in the context of chosen type with the second. So, Mohit, if some unknown queries or code involved I'd better leave them as they are Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 ASC' - MS> does this mean that in a sense IDX1 is a subset of IDX2 and can be removed? If you wanted to have just one index, rather than two, then you could have: 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. I think this is what this page is saying in sections 1.0 and 1.1: http://www.sqlite.org/optoverview.html If you had an index (type, name ASC), then a query which used column name, but didn't use column type would not get to use the index. So, yes you could get away with one index. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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> 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 queries you will be doing, so there is no > way I can tell if you need these indexes. Columns which are used in WHERE > clauses are good candidates for indexing, generally speaking. > > You don't show a column called search_name in the CREATE TABLE line. I > assume there is one. The second index should be: CREATE INDEX IDX2 on tx(type, name ASC); What I had meant to ask was whether there is any benefit in having two indexes when one of the indexes is exactly within the other. IDX1 is index on 'name ASC' while IDX2 is an index on 'type, name ASC' - does this mean that in a sense IDX1 is a subset of IDX2 and can be removed? > A more specific answer would require more information, but I hope this > helps. I apologize for the mistake I made in sending out the schema. Thanks again for taking the time to answer. Best Regards, Mohit. 26/11/2010 | 5:58 PM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 queries you will be doing, so there is no way I can tell if you need these indexes. Columns which are used in WHERE clauses are good candidates for indexing, generally speaking. You don't show a column called search_name in the CREATE TABLE line. I assume there is one. A more specific answer would require more information, but I hope this helps. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 that IDX1 is not necessary in this case. Would there be any (significant) benefit in retaining that index? Thanks, Mohit. 26/11/2010 | 2:54 PM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
