Re: [sqlite] Indexes on columns
Ahh..ok, thanks for the response everyone. I really appreciate the help here :). On Fri, Jun 24, 2011 at 11:10 AM, Igor Tandetnik wrote: > On 6/24/2011 1:58 PM, logan...@gmail.com > wrote: > > Sorry, but seems like I'm missing something here. > > > > From my understanding it looks like for Integer ID columns that are PK > > SQLite doesn't generate any indexes. Is this true? > > It's true in a narrow technical sense, but it doesn't matter in practice. > > In SQLite, data is organized in B-trees. Each table and each index is a > B-tree. For an index, the key into that B-tree is the set of fields the > index is built on. For a table, each row has a unique integer > identifier, usually referred to as RowId, which serves as a key into the > table's B-tree. Looking up a row in the table by its RowId is as fast as > looking up an index entry by its key, because it's really the same > operation. > > When you declare a column as INTEGER PRIMARY KEY, SQlite simply makes it > an alias for an already-existing, always-present RowId column. Again, > the table itself essentially acts as an index on this column, no > additional external data structure is necessary. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
On 6/24/2011 1:58 PM, logan...@gmail.com wrote: > Sorry, but seems like I'm missing something here. > > From my understanding it looks like for Integer ID columns that are PK > SQLite doesn't generate any indexes. Is this true? It's true in a narrow technical sense, but it doesn't matter in practice. In SQLite, data is organized in B-trees. Each table and each index is a B-tree. For an index, the key into that B-tree is the set of fields the index is built on. For a table, each row has a unique integer identifier, usually referred to as RowId, which serves as a key into the table's B-tree. Looking up a row in the table by its RowId is as fast as looking up an index entry by its key, because it's really the same operation. When you declare a column as INTEGER PRIMARY KEY, SQlite simply makes it an alias for an already-existing, always-present RowId column. Again, the table itself essentially acts as an index on this column, no additional external data structure is necessary. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
On 24 Jun 2011, at 6:58pm, logan...@gmail.com wrote: > From my understanding it looks like for Integer ID columns that are PK > SQLite doesn't generate any indexes. Is this true? > > If the above is true then I want to create an index to improve the perf of > my queries that are run against it. The indexes are generated and SQLite will use them internally whenever it finds them convenient. It's just that they are not given names, so you can find out anything about them yourself. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
On Jun 24, 2011, at 1:58 PM, logan...@gmail.com wrote: > Sorry, but seems like I'm missing something here. > > From my understanding it looks like for Integer ID columns that are PK > SQLite doesn't generate any indexes. Is this true? > No, what you think is not true. SQLite does generate an index for INTEGER PRIMARY KEY columns. > If the above is true then I want to create an index to improve the perf of > my queries that are run against it. > > Thanks, > Hitesh > > On Fri, Jun 24, 2011 at 5:31 AM, Igor Tandetnik wrote: > >> logan...@gmail.com wrote: >>> Yes, that's exactly what it is. Here is the definition of one of the >> table: >>> >>> CREATE TABLE [Attributes] ( >>> [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, >>> [Name] VARCHAR(50) NOT NULL >>> ) >>> >>> Will creating explicit index on Id fix this issue? >> >> What issue? Why is having an explicit index, separate from that built into >> the table itself, important to you? What exactly do you feel is wrong with >> the way things are now? >> -- >> Igor Tandetnik >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
Sorry, but seems like I'm missing something here. >From my understanding it looks like for Integer ID columns that are PK SQLite doesn't generate any indexes. Is this true? If the above is true then I want to create an index to improve the perf of my queries that are run against it. Thanks, Hitesh On Fri, Jun 24, 2011 at 5:31 AM, Igor Tandetnik wrote: > logan...@gmail.com wrote: > > Yes, that's exactly what it is. Here is the definition of one of the > table: > > > > CREATE TABLE [Attributes] ( > > [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > > [Name] VARCHAR(50) NOT NULL > > ) > > > > Will creating explicit index on Id fix this issue? > > What issue? Why is having an explicit index, separate from that built into > the table itself, important to you? What exactly do you feel is wrong with > the way things are now? > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
logan...@gmail.com wrote: > Yes, that's exactly what it is. Here is the definition of one of the table: > > CREATE TABLE [Attributes] ( > [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > [Name] VARCHAR(50) NOT NULL > ) > > Will creating explicit index on Id fix this issue? What issue? Why is having an explicit index, separate from that built into the table itself, important to you? What exactly do you feel is wrong with the way things are now? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
Yes, that's exactly what it is. Here is the definition of one of the table: CREATE TABLE [Attributes] ( [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Name] VARCHAR(50) NOT NULL ) Will creating explicit index on Id fix this issue? Thanks. On Thu, Jun 23, 2011 at 11:07 PM, Dan Kennedy wrote: > On 06/24/2011 12:26 PM, logan...@gmail.com wrote: > > Hello, > > > > My understanding is that an index is automatically created on any column > > that is used in the primary key (or a composite index is created if the > key > > is composed of different columns). If this is correct then why don't I > see > > indexes for those in my table (I'm using SQLite Administrator and Firefox > > plugin based SQLite manager). I do see indexes for the columns that I > added > > a unique constraint upon. > > > > Is the above just a GUI error in these tools or an index need to be > created > > separately on the columns used in primary keys? > > Maybe your tables have "integer primary keys". Those are an exception > See here: > > http://www.sqlite.org/lang_createtable.html#rowid > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
On 06/24/2011 12:26 PM, logan...@gmail.com wrote: > Hello, > > My understanding is that an index is automatically created on any column > that is used in the primary key (or a composite index is created if the key > is composed of different columns). If this is correct then why don't I see > indexes for those in my table (I'm using SQLite Administrator and Firefox > plugin based SQLite manager). I do see indexes for the columns that I added > a unique constraint upon. > > Is the above just a GUI error in these tools or an index need to be created > separately on the columns used in primary keys? Maybe your tables have "integer primary keys". Those are an exception See here: http://www.sqlite.org/lang_createtable.html#rowid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexes on columns
Hello, My understanding is that an index is automatically created on any column that is used in the primary key (or a composite index is created if the key is composed of different columns). If this is correct then why don't I see indexes for those in my table (I'm using SQLite Administrator and Firefox plugin based SQLite manager). I do see indexes for the columns that I added a unique constraint upon. Is the above just a GUI error in these tools or an index need to be created separately on the columns used in primary keys? Thanks, Hitesh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes with WHERE, GROUP BY, ORDER BY
Aly Hirani wrote: > I had 2 questions: > > 1) Given the query: > > SELECT col1 FROM table WHERE col2 = ? GROUP BY col3, col4 ORDER BY col5, > col6, col7, col8; What does this query mean? If col5, col6, col7 and col8 vary within a group defined by col3, col4, how exactly should these groups be ordered? > What would be the "right" index to create? > > I was thinking it would be: > > CREATE INDEX index1 ON table (col2, col3, col4, col5, col6, col7, col8); Yes, this or any initial prefix thereof. > 2) If there are no aggregates column present in a query, are GROUP BY > essentially ORDER BYs semantically? (such as in the query above) No. ORDER BY doesn't change the number of rows returned, GROUP BY may. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexes with WHERE, GROUP BY, ORDER BY
Hello everyone, I had 2 questions: 1) Given the query: SELECT col1 FROM table WHERE col2 = ? GROUP BY col3, col4 ORDER BY col5, col6, col7, col8; What would be the "right" index to create? I was thinking it would be: CREATE INDEX index1 ON table (col2, col3, col4, col5, col6, col7, col8); Running an EXPLAIN QUERY PLAN indicates that this index is in fact used. However, what I cannot decipher from the VBDE codes from EXPLAIN is whether it is used fully to satisfy the entire query or is it used to only satisfy part of the query. 2) If there are no aggregates column present in a query, are GROUP BY essentially ORDER BYs semantically? (such as in the query above) Thanks a lot folks! Aly Hirani ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes problem in unicode extension support
Hello! On Friday 18 September 2009 20:05:15 Jean-Christophe Deschamps wrote: > Alexey, > > >I'm using extension for base unicode support > >(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two > >releases find the problem with indexes by columns with redefined > >NOCASE collation > > This code has many problems and the version on your site (the same > version is available elsewhere) doesn't work as expected. NOCASE is > not the only function with problems, there are more with UPPER, > LOWER. Even some tries have wrong data. I had to recompile three of them. This module is based on code from http://ioannis.mpsounds.net/blog/2007/12/19/sqlite-native-unicode-like-support/ > I now have some time to finish it and it should be ready for beta > anytime soon. > > I compile for Windows 32 with MinGW gcc but it shouldn't be very hard > for someone to make it work on another OS as well. I can test it on debian lenny. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes problem in unicode extension support
Alexey, >I'm using extension for base unicode support >(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two >releases find the problem with indexes by columns with redefined >NOCASE collation This code has many problems and the version on your site (the same version is available elsewhere) doesn't work as expected. NOCASE is not the only function with problems, there are more with UPPER, LOWER. Even some tries have wrong data. I had to recompile three of them. I was in need of such extension (and a bit more), started looking at open source code and then began work on it. Unfortunately I had to switch to other boring but urgent tasks, and for really much longer than I would have liked. I now have some time to finish it and it should be ready for beta anytime soon. The module offers some universal Unicode support: UPPER LOWER UNACCENT FOLD LIKE(unaccented version) GLOB(unaccented version) TYPOS (unaccented lowercased Damerau-Levenshtein distance on strings with support for '_' and trailing '%' as in LIKE) NOCASE LETTERS (a simple wrapper to a Windows function for locale-independant unaccented collation) specific handling for small German sharp s 'ß' I compile for Windows 32 with MinGW gcc but it shouldn't be very hard for someone to make it work on another OS as well. JcD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexes problem in unicode extension support
Hello!
I'm using extension for base unicode support
(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two releases find
the problem with indexes by columns with redefined NOCASE collation
(groups.name autoindex and composite index with
const_telephony_direction.name):
=
$ sqlite3 :memory:
SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE groups
...> (
...> name TEXT collate NOCASE UNIQUE NOT NULL DEFAULT ''
...> );
sqlite> INSERT INTO "groups" VALUES('Администраторы');
sqlite> CREATE TABLE const_telephony_direction (
...> name text not null,
...> destcode text not null
...> );
sqlite> INSERT INTO "const_telephony_direction"
VALUES('Совинтел_МГМН_старые_кмст','7485');
sqlite> CREATE INDEX const_telephony_direction_complex_idx on
const_telephony_direction(name,destcode);
sqlite> COMMIT;
sqlite>
sqlite> pragma integrity_check;
rowid 1 missing from index sqlite_autoindex_groups_1
=
Without indexes on redefined NOCASE collated fields this database is correct:
=
$ sqlite3 :memory:
SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE groups
...> (
...> name TEXT collate NOCASE NOT NULL DEFAULT ''
...> );
sqlite> INSERT INTO "groups" VALUES('Администраторы');
sqlite> CREATE TABLE const_telephony_direction (
...> name text not null,
...> destcode text not null
...> );
sqlite> INSERT INTO "const_telephony_direction"
VALUES('Совинтел_МГМН_старые_кмст','7485');
sqlite> CREATE INDEX const_telephony_direction_complex_idx on
const_telephony_direction(destcode);
sqlite> COMMIT;
sqlite>
sqlite> pragma integrity_check;
ok
=
Can anybody help me to to fix it?
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on the table
Thanks Simon for detail explaination about the indexes! JP From: Simon Slavin To: General Discussion of SQLite Database Sent: Tuesday, July 21, 2009 3:57:22 PM Subject: Re: [sqlite] Indexes on the table On 21 Jul 2009, at 11:12pm, Joanne Pham wrote: > CREATE TABLE myTable( > startTime INTEGER ... > appId INTEGER > myId INTEGER ... > trafficType INTEGER > .. > ) > StartTime can be from 1...59 > appId can be from 1...256 > myId can be from 1...5000 > trafficType can be from 1..3 > > I would like to create index for this table on these columns > StartTime ,appId, myId, trafficType as : > create unique index myTableIndex on myTable(appId, myId, > trafficType, startTime). > Is the order of the columns in the create index statement > importance? If yes then what is rule of thumb here? You choose what indexes to create depending on what SELECT commands you're going to use. So if none of your SELECT instructions use trafficType in the WHERE or ORDER BY clause there is no need for it in any index. Once you know which fields you want in an index, the principle is to reject as many rows as you can as soon as you can. This leaves the software fewer records to worry about at the next step, which means it needs less memory and has less processing to do. Suppose you have a thousand records and want something like SELECT * FROM myTable WHERE appId = 40 AND trafficType = 2 Suppose 1/3rd of your records have each traffic type, but 1/256th of your records have each appId. Then selecting on trafficType first would reject 2 records out of every 3, meaning that the next step has to process just 333 records, which is good. But selecting on appId first instead would reject 255 records out of every 256, meaning that the next step has to process just 4 records which is much better. So in this case an index on (appId, trafficType) would be research in a faster SELECT than (trafficType, appId). Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on the table
On 21 Jul 2009, at 11:12pm, Joanne Pham wrote: > CREATE TABLE myTable( > startTime INTEGER ... > appId INTEGER > myId INTEGER ... > trafficType INTEGER > .. > ) > StartTime can be from 1...59 > appId can be from 1...256 > myId can be from 1...5000 > trafficType can be from 1..3 > > I would like to create index for this table on these columns > StartTime ,appId, myId, trafficType as : > create unique index myTableIndex on myTable(appId, myId, > trafficType, startTime). > Is the order of the columns in the create index statement > importance? If yes then what is rule of thumb here? You choose what indexes to create depending on what SELECT commands you're going to use. So if none of your SELECT instructions use trafficType in the WHERE or ORDER BY clause there is no need for it in any index. Once you know which fields you want in an index, the principle is to reject as many rows as you can as soon as you can. This leaves the software fewer records to worry about at the next step, which means it needs less memory and has less processing to do. Suppose you have a thousand records and want something like SELECT * FROM myTable WHERE appId = 40 AND trafficType = 2 Suppose 1/3rd of your records have each traffic type, but 1/256th of your records have each appId. Then selecting on trafficType first would reject 2 records out of every 3, meaning that the next step has to process just 333 records, which is good. But selecting on appId first instead would reject 255 records out of every 256, meaning that the next step has to process just 4 records which is much better. So in this case an index on (appId, trafficType) would be research in a faster SELECT than (trafficType, appId). Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexes on the table
Hi All, I need to create the indexes on the tables and these indexes have 4 columns. Let say the table definition as below: CREATE TABLE myTable( startTime INTEGER ... appId INTEGER myId INTEGER ... trafficType INTEGER .. ) StartTime can be from 1...59 appId can be from 1...256 myId can be from 1...5000 trafficType can be from 1..3 I would like to create index for this table on these columns StartTime ,appId, myId, trafficType as : create unique index myTableIndex on myTable(appId, myId, trafficType, startTime). Is the order of the columns in the create index statement importance? If yes then what is rule of thumb here? Thanks JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes lifespan and reindexing...
On May 8, 2009, at 3:31 PM, ioannis wrote: > I would like to clarify my understanding in regards to lifespan of > indexes. > > 1. Once the index is created, the sequence of the items is stored in > the > database, so the following example would be possible. > a) Create an index with an overloaded NOCASE collation function on a > column. > b) Sort the column with NOCASE, without loading the overloaded NOCASE > collation extension. > c) The sorting is expected to follow the sequence created initially > by the > overloaded collation function and not the standard function. > > 2. I am a bit unclear what happens to an already created index once i > INSERT, UPDATE, DELETE a row in an indexed column. > 3. When is a REINDEX required (except when collation function > changes) ? I don't really understand your question. But I think the answer might be captured in these facts: * Whenever you change a collating function (by overloading it) you must run REINDEX before doing any INSERT, UPDATE, or DELETE operations or you will might corrupt the database file. * The only time you should ever need to run REINDEX is after changing a collating function. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexes lifespan and reindexing...
I would like to clarify my understanding in regards to lifespan of indexes. 1. Once the index is created, the sequence of the items is stored in the database, so the following example would be possible. a) Create an index with an overloaded NOCASE collation function on a column. b) Sort the column with NOCASE, without loading the overloaded NOCASE collation extension. c) The sorting is expected to follow the sequence created initially by the overloaded collation function and not the standard function. 2. I am a bit unclear what happens to an already created index once i INSERT, UPDATE, DELETE a row in an indexed column. 3. When is a REINDEX required (except when collation function changes) ? Best Regards, ioannis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexes questions
Hi All, I have the table which has the following indexes: CREATE INDEX Zactivate ON sig (peerid,flowid,fbid); CREATE INDEX Zfbid ON sig (flowid,fbid); CREATE INDEX Zsignature ON sig (peerid,Zsignature); And below are where statements: WHERE Zsignature = ? AND peerid = ?"; WHERE peerid = ?" WHERE peerid = ?"; WHERE flowid = ? AND peerid = ?"; WHERE flowid = ? AND peerid = ?"; WHERE flowid = ? AND peerid = ?"; WHERE flowid = ? AND peerid = ?"; WHERE flowid=? AND fbid=? AND peerid=?"; WHERE peerid=? AND stale='2'"; WHERE peerid=? AND flowid=? AND stale='2'"; Should Ionly need two indexes. The second one should not be there. Now the write operation will be very slow. Any ideas? Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes not being used after INNER JOINS?
On 12/31/07, Hugo Ferreira <[EMAIL PROTECTED]> wrote: > a) It should be completely integrated/embedded within the application; no > separate install. Just a single .DLL ;-) > b) It must have bindings with .Net 2.0 and Mono >1.2.5. > c) Open-Source. On the subject of alternatives, Firebird might fit the bill. What I'm unsure of is its embedded support on multiple platforms; last time I looked (some years ago) embedded was only working fully on Windows. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Indexes not being used after INNER JOINS?
@Scott: Id is a primary key. I believe PKs always have indexes. @Griggs: While the database engine is to be run on a full blown PC, I have three needs that have lead me to choose SQLite: a) It should be completely integrated/embedded within the application; no separate install. Just a single .DLL ;-) b) It must have bindings with .Net 2.0 and Mono >1.2.5. c) Open-Source. Switching to a client-server application like PostgreSQL that can't be embedded into the application would actually be my last resort. Cheers and thanks! Hugo Ferreira On Dec 31, 2007 11:56 PM, Scott Baker <[EMAIL PROTECTED]> wrote: > Hugo Ferreira wrote: > > Hi everyone, > > > > I seem to be having a problem here with LEFT JOINS between tables and > > results of INNER JOINS. Take for example the following example (table > > definition is in the end): > > > > TABLE COUNT esparqueologico: 750 > > TABLE COUNT data: 3828 > > TABLE COUNT reftemporal: 3972 > > > > This query would take 6.7s to run (750 rows): > > > > select * from esparqueologico oe left join > > (data d cross join reftemporal r on d.reftemporal_id = r.id) x > > on oe.datacao_id = x.id > > > > However this takes virtually zero time (750 rows): > > > > select * from esparqueologico oe left join data d on oe.datacao_id = > d.id > > > > And this takes 0.1s (3828 rows): > > > > select * from data d inner join reftemporal r on d.reftemporal_id = r.id > > > > It seems to me that indexes are lost in the first query. Here is the > data > > definition I'm using: > > > > CREATE TABLE data ( > > id guid NOT NULL, > > reftemporal_id guid NOT NULL, > > PRIMARY KEY (id), > > FOREIGN KEY (reftemporal_id) REFERENCES reftemporal(id), > > ) > > > > CREATE TABLE reftemporal ( > > id guid NOT NULL, > > subtype varchar, > > PRIMARY KEY (id) > > ) > > > > CREATE TABLE esparqueologico ( > > id guid NOT NULL, > > datacao_id guid, > > PRIMARY KEY (id), > > FOREIGN KEY (datacao_id) REFERENCES data(id), > > ) > > > > CREATE INDEX idx_data_reftemporal_id ON data(reftemporal_id) > > CREATE INDEX idx_esparqueologico_datacao_id ON > esparqueologico(datacao_id) > > I'm not an expert, but don't you want an index on reftemporal.id as > well? You're querying it in your JOIN clause, but there's no index > on the field. > > -- > Scott Baker - Canby Telcom > RHCE - System Administrator - 503.266.8253 > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > -- スプーンが ない
Re: [sqlite] Indexes not being used after INNER JOINS?
Hugo Ferreira wrote: > Hi everyone, > > I seem to be having a problem here with LEFT JOINS between tables and > results of INNER JOINS. Take for example the following example (table > definition is in the end): > > TABLE COUNT esparqueologico: 750 > TABLE COUNT data: 3828 > TABLE COUNT reftemporal: 3972 > > This query would take 6.7s to run (750 rows): > > select * from esparqueologico oe left join > (data d cross join reftemporal r on d.reftemporal_id = r.id) x > on oe.datacao_id = x.id > > However this takes virtually zero time (750 rows): > > select * from esparqueologico oe left join data d on oe.datacao_id = d.id > > And this takes 0.1s (3828 rows): > > select * from data d inner join reftemporal r on d.reftemporal_id = r.id > > It seems to me that indexes are lost in the first query. Here is the data > definition I'm using: > > CREATE TABLE data ( > id guid NOT NULL, > reftemporal_id guid NOT NULL, > PRIMARY KEY (id), > FOREIGN KEY (reftemporal_id) REFERENCES reftemporal(id), > ) > > CREATE TABLE reftemporal ( > id guid NOT NULL, > subtype varchar, > PRIMARY KEY (id) > ) > > CREATE TABLE esparqueologico ( > id guid NOT NULL, > datacao_id guid, > PRIMARY KEY (id), > FOREIGN KEY (datacao_id) REFERENCES data(id), > ) > > CREATE INDEX idx_data_reftemporal_id ON data(reftemporal_id) > CREATE INDEX idx_esparqueologico_datacao_id ON esparqueologico(datacao_id) I'm not an expert, but don't you want an index on reftemporal.id as well? You're querying it in your JOIN clause, but there's no index on the field. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Indexes not being used after INNER JOINS?
On 12/31/07, Hugo Ferreira <[EMAIL PROTECTED]> wrote: > Yes, indeed, it is doing a sequential scan according to EXPLAIN. Still, the > slowness is unbelievable in such a small database. The whole db takes 11Mb, > and doing a LEFT JOIN between a few hundred and a few thousand of rows on a > "Core 2 Duo" taking 6 seconds is... I don't even know what it is :P There > must be something very strange going on... I suppose it is not possible to > create indexes on views, right? > > I'll play some more with queries and try to figure out if I can tweak this. > If not, then I guess I'll unfortunately have to move to PostgreSQL :-( If you haven't found this page yet, it may be useful: http://sqlite.org/optoverview.html If a client-server database engine like PostgreSQL is better suited to your application, I'd probably use it anyway. SQLite is good, but it's not a compact version of a major database engine. If you're simply looking for something that's easily deployable, and SQLite turns out to not meet your needs, there are other database engines that might. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Indexes not being used after INNER JOINS?
Hey! Yes, indeed, it is doing a sequential scan according to EXPLAIN. Still, the slowness is unbelievable in such a small database. The whole db takes 11Mb, and doing a LEFT JOIN between a few hundred and a few thousand of rows on a "Core 2 Duo" taking 6 seconds is... I don't even know what it is :P There must be something very strange going on... I suppose it is not possible to create indexes on views, right? I'll play some more with queries and try to figure out if I can tweak this. If not, then I guess I'll unfortunately have to move to PostgreSQL :-( Cheers! Hugo Ferreira On Dec 31, 2007 8:42 PM, Griggs, Donald <[EMAIL PROTECTED]> wrote: > Hello Hugo, > > If you preceed a SELECT with the string >EXPLAIN QUERY PLAN > sqlite will make it clear which, if any, indices it would use when > running the select. > > Sqlite, unlike some of the "non-light" databases, uses a maxium of one > index per table per select, I believe. > > You may want to look at the ANALYZE command. > > You really do want a CROSS JOIN in the first case, is that right? > > I'm not sure if this is info you're looking for. > > Regards, and Happy New Year to all, > Donald G. > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > -- スプーンが ない
RE: [sqlite] Indexes not being used after INNER JOINS?
Hello Hugo, If you preceed a SELECT with the string EXPLAIN QUERY PLAN sqlite will make it clear which, if any, indices it would use when running the select. Sqlite, unlike some of the "non-light" databases, uses a maxium of one index per table per select, I believe. You may want to look at the ANALYZE command. You really do want a CROSS JOIN in the first case, is that right? I'm not sure if this is info you're looking for. Regards, and Happy New Year to all, Donald G. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Indexes not being used after INNER JOINS?
Hi everyone, I seem to be having a problem here with LEFT JOINS between tables and results of INNER JOINS. Take for example the following example (table definition is in the end): TABLE COUNT esparqueologico: 750 TABLE COUNT data: 3828 TABLE COUNT reftemporal: 3972 This query would take 6.7s to run (750 rows): select * from esparqueologico oe left join (data d cross join reftemporal r on d.reftemporal_id = r.id) x on oe.datacao_id = x.id However this takes virtually zero time (750 rows): select * from esparqueologico oe left join data d on oe.datacao_id = d.id And this takes 0.1s (3828 rows): select * from data d inner join reftemporal r on d.reftemporal_id = r.id It seems to me that indexes are lost in the first query. Here is the data definition I'm using: CREATE TABLE data ( id guid NOT NULL, reftemporal_id guid NOT NULL, PRIMARY KEY (id), FOREIGN KEY (reftemporal_id) REFERENCES reftemporal(id), ) CREATE TABLE reftemporal ( id guid NOT NULL, subtype varchar, PRIMARY KEY (id) ) CREATE TABLE esparqueologico ( id guid NOT NULL, datacao_id guid, PRIMARY KEY (id), FOREIGN KEY (datacao_id) REFERENCES data(id), ) CREATE INDEX idx_data_reftemporal_id ON data(reftemporal_id) CREATE INDEX idx_esparqueologico_datacao_id ON esparqueologico(datacao_id) Cheers, Hugo Ferreira
[sqlite] INDEXES and PRIMARY KEY
Hi, guys. Can anybody explain me for what PRIMARY KEY needed? For example, is there some pluses using PRIMARY KEY insted of a simple INTEGER column (when I connect two tables by values of this column in SELECT queries)? And is PRIMARY KEY auto increment his value when inserting new value in a table? I mean can I use INTEGER PRIMARY KEY and set him by himself? And for what a INDEX? P.S. Sorry for so newbies questions :) -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Indexes usage on Foreign Key
I am using sqlite v3.1.3 on Mac OS 10.4 Consider the following schema Create table Library(LibraryID INTEGER PRIMARY KEY AUTOINCRMENT, LibraryName TEXT); Create table Book(LibraryID INTEGER REFERENCES Library, BookID TEXT PRIMARY KEY, BookName TEXT); Now I believe Library(LibraryID) is automatically indexed. But when I use Book(LibrayID) field in one of my Query as below I believe it is not indexed as the query execution takes more amount of time. eg: SELECT * FROM Book WHERE LibraryID IN ( ... ); But when I index Book(LibraryID) it is quick enough. But is it meaningful to index Foreign Key ? Will the Foreign key use the same index as of its counterpart in original table or should it be separately indexed ? I am not able to see the Query plan through "Explain Query plan" & ".explain" command. " Explain Query plan" returns a syntax error and .explain does nothing :( Thanks in advance, Bharath Booshan L. --- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] indexes in memory
- Original Message From: Christian Smith <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, November 9, 2006 8:38:51 AM Subject: Re: [sqlite] indexes in memory > chetana bhargav uttered: > > Hi, > > > I have a question regrading indexes, > > > > When I open a connection, > > > > Will indexes be loaded into memory. If one of the tables in the DB, the > > connection for which I have opened, has an index. > > If, so is there any way to selectively load/unload that from memory. > > > Indexes will be loaded into the cache as needed. The whole SQLite database > is page based, and the cache caches the pages. The tables and indexes are > implemented as page based btrees, with nodes represented by pages. > > The cache is unaware of the higher level structure of the btrees, and > there is no way to selectively bring load/unload tables or indexes from > memory. The page cache will manage itself on an LRU basis. > > > ... > > Chetana. > Christian I found that when opening your connection, if you're about to do a lot of operations it can be worth doing a "SELECT keyname FROM ... "over the whole data to prepopulate the cache with the index data. Even on pretty large datasets this only takes a few seconds and the following operations will be much faster (and the overall time to complete the batch is much smaller). Nicolas
Re: [sqlite] indexes in memory
chetana bhargav uttered: Hi, I have a question regrading indexes, When I open a connection, Will indexes be loaded into memory. If one of the tables in the DB, the connection for which I have opened, has an index. If, so is there any way to selectively load/unload that from memory. Indexes will be loaded into the cache as needed. The whole SQLite database is page based, and the cache caches the pages. The tables and indexes are implemented as page based btrees, with nodes represented by pages. The cache is unaware of the higher level structure of the btrees, and there is no way to selectively bring load/unload tables or indexes from memory. The page cache will manage itself on an LRU basis. ... Chetana. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] indexes in memory
Hi, I have a question regrading indexes, When I open a connection, Will indexes be loaded into memory. If one of the tables in the DB, the connection for which I have opened, has an index. If, so is there any way to selectively load/unload that from memory. ... Chetana.
Re: [sqlite] Indexes analysis
chetana bhargav <[EMAIL PROTECTED]> wrote: > > And regarding EXPLAIN QUERY METHOD, I seem to be getting some error. It just > says, > > "0|0|TABLE table_acc WITH INDEX IDX_ACC_ID_STATE" > This is not an error. It is the output of EXPLAIN QUERY PLAN. This says that you are reading the table "table_acc" and that it is using the "idx_acc_id_state" to speed the search of that table. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Indexes analysis
Hi, I am trying to analyze index usage for my queries for performance. Basically I found two methods, One of them EXPLAIN QUERY METHOD. And the other is idxChk tool. My DB is of latest version of 3.3.8. When I checked for idxChk tool page it says it was tested only till 3.2.7. Just wanted to know is it still supported on latest version also. And regarding EXPLAIN QUERY METHOD, I seem to be getting some error. It just says, "0|0|TABLE table_acc WITH INDEX IDX_ACC_ID_STATE" But when I try to use EXPLAIN its giving complete info. Just wanted to know whats the error. Is the above statement is correct or what? Is there any way to interpret this. ... Chetana.
Re: [sqlite] indexes with ORDER BY on columns from different tables
On 8/19/06, Gerry Snyder <[EMAIL PROTECTED]> wrote: Jonathan Ellis wrote: > (was the "+" some kind of shorthand I'm unfamiliar with?) > Yes. It tells sqlite not to use those fields as an index. Ah, I see. When I tried "+f.mtime" it gave a "no such column" error, but that was because f was in the subquery now. So, now I've tried it with various combinations of + but I still can't get the file name index used. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] indexes with ORDER BY on columns from different tables
Jonathan Ellis wrote: On 8/19/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Jonathan Ellis" <[EMAIL PROTECTED]> wrote: > When ordering by columns from two tables, sqlite isn't using the index > on the first column. > > explain query plan SELECT * > FROM files f, file_info fi > WHERE f.id = fi.file_id > ORDER BY f.name, fi.mtime; > > > Is there a workaround? Try this: SELECT * FROM ORDER BY +f.name, +fi.mtime; Doesn't seem to help, if I've understood correctly: explain query plan SELECT * FROM ( SELECT * FROM files f, file_info fi WHERE f.id = fi.file_id) ORDER BY name, mtime; 0|0|TABLE files AS f 1|1|TABLE file_info AS fi WITH INDEX info_by_file (was the "+" some kind of shorthand I'm unfamiliar with?) Yes. It tells sqlite not to use those fields as an index. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] indexes with ORDER BY on columns from different tables
On 8/19/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Jonathan Ellis" <[EMAIL PROTECTED]> wrote: > When ordering by columns from two tables, sqlite isn't using the index > on the first column. > > explain query plan SELECT * > FROM files f, file_info fi > WHERE f.id = fi.file_id > ORDER BY f.name, fi.mtime; > > > Is there a workaround? Try this: SELECT * FROM ORDER BY +f.name, +fi.mtime; Doesn't seem to help, if I've understood correctly: explain query plan SELECT * FROM ( SELECT * FROM files f, file_info fi WHERE f.id = fi.file_id) ORDER BY name, mtime; 0|0|TABLE files AS f 1|1|TABLE file_info AS fi WITH INDEX info_by_file (was the "+" some kind of shorthand I'm unfamiliar with?)
Re: [sqlite] indexes with ORDER BY on columns from different tables
"Jonathan Ellis" <[EMAIL PROTECTED]> wrote: > When ordering by columns from two tables, sqlite isn't using the index > on the first column. > > explain query plan SELECT * > FROM files f, file_info fi > WHERE f.id = fi.file_id > ORDER BY f.name, fi.mtime; > > > Is there a workaround? Try this: SELECT * FROM ORDER BY +f.name, +fi.mtime; -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] indexes with ORDER BY on columns from different tables
When ordering by columns from two tables, sqlite isn't using the index on the first column. explain query plan SELECT * FROM files f, file_info fi WHERE f.id = fi.file_id ORDER BY f.name, fi.mtime; 0|0|TABLE files AS f 1|1|TABLE file_info AS fi WITH INDEX info_by_file If I drop the secondary order (to simply "ORDER BY f.name") then it does use the index: 0|0|TABLE files AS f WITH INDEX files_by_name ORDER BY 1|1|TABLE file_info AS fi WITH INDEX info_by_file Is there a workaround? Here are sample tables: CREATE TABLE files ( id integer PRIMARY KEY, nametext NOT NULL ); CREATE INDEX files_by_name on files(name); CREATE TABLE file_info ( file_id int NOT NULL REFERENCES files, mtime int NOT NULL ); CREATE INDEX info_by_file on file_info(file_id); - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] indexes
> Some DBMS's (PostgreSQL comes to mind) do allow you to create an index > which also forces the data to be ordered. This can speed up some > patterns of read access, but makes inserts quite expensive and should be > used only with extreme caution. MS Sqlserver calls them 'clustered indexes'. They're a good idea if you use them correctly.
Re: [sqlite] indexes
Some DBMS's (PostgreSQL comes to mind) do allow you to create an index which also forces the data to be ordered. This can speed up some patterns of read access, but makes inserts quite expensive and should be used only with extreme caution. chetana bhargav wrote: Thanks for the info.
Re: [sqlite] indexes
Thanks for the info. Cheers, Chetana. Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 4/17/06, chetana bhargav wrote: > Am I correct in saying that once we create an index on a table what ever the > new records added would be done according to the index mentioned. > > If so, if we create an index on a table which has already some records then > will those records be re-arranged. Records are not reordered or moved based on the index. The index is like an index in a book. It tells where to find things but doesn't change the order. - How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
Re: [sqlite] indexes
On 4/17/06, chetana bhargav <[EMAIL PROTECTED]> wrote: > Am I correct in saying that once we create an index on a table what ever the > new records added would be done according to the index mentioned. > > If so, if we create an index on a table which has already some records then > will those records be re-arranged. Records are not reordered or moved based on the index. The index is like an index in a book. It tells where to find things but doesn't change the order.
Re: [sqlite] indexes
Am I correct in saying that once we create an index on a table what ever the new records added would be done according to the index mentioned. If so, if we create an index on a table which has already some records then will those records be re-arranged. Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 4/17/06, chetana bhargav wrote: > I just want to know, if we create an index on any table, how the index is > stored, is it stored in a seperate file or as part of the current table only > in the same file. There's only one file no matter how many tables or indexes you create. - Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries for just 2¢/min with Yahoo! Messenger with Voice.
Re: [sqlite] indexes
On 4/17/06, chetana bhargav <[EMAIL PROTECTED]> wrote: > I just want to know, if we create an index on any table, how the index is > stored, is it stored in a seperate file or as part of the current table only > in the same file. There's only one file no matter how many tables or indexes you create.
[sqlite] indexes
Hi, I just want to know, if we create an index on any table, how the index is stored, is it stored in a seperate file or as part of the current table only in the same file. Cheers, Chetana - New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
[sqlite] Indexes
Hi, My current database is mainly names and addresses. Most of my record viewing is done by: "select * from TableName order by Zip,Address,No" Zip, Address and No (house number) have separate indexes. On other databases I have used, I have combined these three columns into one index, and used that index to display records I tried creating a new index, "create index DefView on TableName(Zip,Address,No)" SQLite didn't complain about that, but when I tried to use the index, by using "select * from TableName order by DefView" I didn't get any records returned to view I have obviously got the wrong end of the stick about indexes! Could someone put me right, or point me gently in the right direction? Thanks, Brian Pugh
