Re: [sqlite] Indexing multiple values per row

> On Mar 15, 2018, at 11:27 AM, Jay Kreibich wrote: > > Recognize the fact that if you’re storing data in a JSON string, to the > database that is just one single value: a string. I am very well aware of that. What _I_ (and my co-workers) are implementing is a higher-level

Re: [sqlite] Indexing multiple values per row

> On Mar 15, 2018, at 12:33 PM, Jens Alfke wrote: > > I'm wondering what the best way is to efficiently search for data values that > can appear multiple times in a table row. SQLite indexes, even expression > indexes, don't directly work for this because they obviously

[sqlite] Indexing multiple values per row

I'm wondering what the best way is to efficiently search for data values that can appear multiple times in a table row. SQLite indexes, even expression indexes, don't directly work for this because they obviously only index one value per row. Traditional relational-database design says to

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

to:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamish Allan Gesendet: Montag, 03. April 2017 16:24 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY Thanks, Hick. I now understand that it's undefined wh

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

index on (b, a) > > The former returns all groups of c with the top one being the one row > returned by the latter. > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Hamish Allan >> Sen

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

f the query > contains more than one min() and/or max() aggregate function. Only the > built-in min() and max() functions work this way." > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von Hamish All

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

is the purpose of the ORDER BY? >>>> >>>> The value of a used for the order by is from some random row in the >>>> grouping of c. Are there relationships between a, b, c, d that you have >>>> not >>>> documented nor told us about? >&g

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

being the one row returned by the latter. > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Hamish Allan > Sent: Monday, 3 April, 2017 02:51 > To: SQLite mailing list > Subject: Re: [sqlite] Indexing WHERE with

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

sers [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamish Allan Gesendet: Montag, 03. April 2017 10:51 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY Ah. My purpose is to determine "d for t

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

t index is on (b, c). The order by is useless. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hamish Allan Sent: Sunday, 2 April, 2017 17:28 To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

ers-boun...@mailinglists.sqlite.org] On Behalf Of Hamish Allan Sent: Sunday, 2 April, 2017 17:28 To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY Given a table: CREATE TABLE x (a INT, b INT, c TEXT, d TEXT); the query: SELECT d FROM x WHERE b = 1 GR

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

seless. > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Hamish Allan >> Sent: Sunday, 2 April, 2017 17:28 >> To: sqlite-users@mailinglists.sqlite.org >> Subject: [sqlite] Indexing WHERE w

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

On 3 Apr 2017, at 12:27am, Hamish Allan wrote: > SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a; Your problem comes down to this: If you are GROUPing BY c, why do you want ORDER BY a ? If you remove the "ORDER BY a" clause then the ideal index would be on (b, c). But

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

. > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Hamish Allan > Sent: Sunday, 2 April, 2017 17:28 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

I had nearly the same question a month ago (Subject: Index usefulness for GROUP BY). In my case, the best index was on the WHERE clause because it eliminated the scan and returned only the few important rows for the other clauses. However, the best result will depend on how many rows are

[sqlite] Indexing WHERE with GROUP BY and ORDER BY

Given a table: CREATE TABLE x (a INT, b INT, c TEXT, d TEXT); the query: SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a; shows the following plan, without indexes: 0|0|0|SCAN TABLE x 0|0|0|USE TEMP B-TREE FOR GROUP BY 0|0|0|USE TEMP B-TREE FOR ORDER BY I can create an index to cover the

Re: [sqlite] indexing for integer column

Hi, Can I create INDEX for the particular column when database journal mode is WAL? Any impact If I INDEXed database with some triggers? Thanks in advance. Best Regards, On Thu, Sep 6, 2012 at 4:04 PM, Durga D wrote: > Thank you Kees. > > > On Thu, Sep 6, 2012

Re: [sqlite] indexing for integer column

Thank you Kees. On Thu, Sep 6, 2012 at 3:34 PM, Kees Nuyt wrote: > On Thu, 6 Sep 2012 12:02:03 +0400, Durga D wrote: > > >Hi All, > > > >Somehow "original author" missed one INTETER column as searchable > >field ( like unique or primary key) in the

Re: [sqlite] indexing for integer column

On Thu, 6 Sep 2012 12:02:03 +0400, Durga D wrote: >Hi All, > >Somehow "original author" missed one INTETER column as searchable >field ( like unique or primary key) in the table. When execute queries >based on this integer field in where clause/joins, huge performance

[sqlite] indexing for integer column

Hi All, Somehow "original author" missed one INTETER column as searchable field ( like unique or primary key) in the table. When execute queries based on this integer field in where clause/joins, huge performance hit. So, I am planning to add INDEXING for this integer column. Is there

Re: [sqlite] Indexing - a test example

On Mar 7, 2011, at 1:58 PM, Richard Hipp wrote: > The beauty of SQL (not just SQLite but any SQL database engine) is that you > can focus on the semantics of your query and not worry about the > implementation - the SQL database engine will figure out the best query > algorithm for you. Ah,

Re: [sqlite] Indexing - a test example

On 7 Mar 2011, at 11:07am, J Trahair wrote: > Picking one of my tables at random (the first one, in fact): > CREATE TABLE CostItems( > RecNo INTEGER PRIMARY KEY AUTOINCREMENT, > CustomerCode TEXT, > ProjectName TEXT, > SupplierName TEXT, > WhatExactly TEXT, > CostDate TEXT > etc. > ); > > I

Re: [sqlite] Indexing - a test example

On Mon, Mar 7, 2011 at 6:07 AM, J Trahair wrote: > > Questions: > 1. Do the differing orders of fields in the 2 SELECTs require me to use 2 > indexes as above? > You'll need two indices if you want your two SELECTs to run efficiently. And the two indices you

Re: [sqlite] Indexing - a test example

SCAN TABLES you're doing good. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of J Trahair [j.trah...@foreversoftware.co.uk] Sent

[sqlite] Indexing - a test example

I have looked at some information on indexing, and watched a 44 minute presentation by Dr Richard Hipp. I looked at the project I am converting to SQLite, and I have identited all the SELECTs for all the tables. They do not fall easily into the 'SELECT x, y, z FROM Table1 WHERE w = 5 AND x = 6

Re: [sqlite] indexing speed

I think you may use FTS3 extension. This is very fast for big tables too. I did test about to 500 millions rows with UUIDs in FTS3 table and results are fine. See as example test here: http://book.mobigroup.ru/dir?name=web_project_DBMS/distributed_schema Some result logs you can find in files

Re: [sqlite] indexing speed

On 2 Aug 2010, at 5:31pm, Paul Sanderson wrote: > I have a table with just a few columns, one is a SHA1 hash and the > second an MD5, there are about 17 Million rows in the table > > if I create an index on the SHA1 column using "create index if not > exists sha1index on hashtable(sha1)" the

Re: [sqlite] indexing speed

Paul Sanderson wrote: > I have a table with just a few columns, one is a SHA1 hash and the > second an MD5, there are about 17 Million rows in the table > > if I create an index on the SHA1 column using "create index if not > exists sha1index on hashtable(sha1)" the

[sqlite] indexing speed

I have a table with just a few columns, one is a SHA1 hash and the second an MD5, there are about 17 Million rows in the table if I create an index on the SHA1 column using "create index if not exists sha1index on hashtable(sha1)" the process takes about 3 minutes, if I follow this immediately by

Re: [sqlite] [sqlite-dev] Poor SQLite indexing performance

I have not done this, but if you have enough RAM available, you might try putting your primary keys in a table in an in-memory database, and test for existence there. That would allow you to enforce uniqueness while postponing creation of the PK index on the disk table until after the initial

Re: [sqlite] [sqlite-dev] Poor SQLite indexing performance

> Also it's quite known that > creating index after inserting all rows is much faster than creating > index before that. So it can be even beneficial in inserting huge > amount of rows somewhere in the middle of the work: first delete all > indexes, then insert rows, then create indexes once more.

Re: [sqlite] [sqlite-dev] Poor SQLite indexing performance

First of all you should write to sqlite-users mailing list, not sqlite-dev. > Why is the indexing so slow and bogs down as we proceed with insertions ? > Any suggestions ? > Also, how could I improve performance ? I can't say exactly why performance with index degrades so significantly with the

Re: [sqlite] Indexing question...

Alan Harris-Reid wrote: > I have a table with a primary key field called artist_id, which is also > marked as autoincrement. If I want an index on this field You don't. There is already an index on this field. SQLite automatically generates an index to enforce PRIMARY KEY and UNIQUE

[sqlite] Indexing question...

I have a table with a primary key field called artist_id, which is also marked as autoincrement. If I want an index on this field (eg. to improve performance when joining with other tables), do I have to index it explicitly, or does the fact that it is already an autoincrement pk field

Re: [sqlite] Indexing for sums?

"Nikolas Stevenson-Molnar" wrote in message news:f45a26bc-1ee8-4a72-a90a-77f40eef6...@evergreen.edu > If I have the following table: > > CREATE TABLE stem(sid integer primary key, x double, y double, dbh > double); > > ... is there any way I can create an index for the

[sqlite] Indexing for sums?

Hi all, If I have the following table: CREATE TABLE stem(sid integer primary key, x double, y double, dbh double); ... is there any way I can create an index for the following query? SELECT * FROM stem WHERE x + dbh > 20 Thanks! _Nik ___

Re: [sqlite] Indexing problem

>> >> >>> kdb "select * from kfz where CRC32=-1509747892;" >>> 48482364|48|0|0C|00|00|0||20|5B93|-1509747892|||0|GP-T 1006|0 >>> 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1 >>> 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1 >> >> What could cause 48482364

Re: [sqlite] Indexing problem

"Marian Aldenhoevel" wrote in message news:49a65fac.5060...@mba-software.de > I am having a strange problem with a sqlite3 database. See the > following transcript: > >> sqlite3 kdb "select * from kfz where kfznr=48482364;" >>

Re: [sqlite] Indexing problem

Hi, >> > CREATE TABLE IF NOT EXISTS KFZ ( > > Is that as reported by the command-line sqlite3 executable program, or > is it from some script that you hope is the one that was used to create > the table? That is from the script I _know_ is the one that created the table. I will send output

Re: [sqlite] Indexing problem

On 26/02/2009 9:45 PM, John Machin wrote: > On 26/02/2009 8:23 PM, Marian Aldenhoevel wrote: >> Hi, >> >> I am having a strange problem with a sqlite3 database. See the following >> transcript: >> >> > sqlite3 kdb "select * from kfz where kfznr=48482364;" >> >

Re: [sqlite] Indexing problem

On 26/02/2009 8:23 PM, Marian Aldenhoevel wrote: > Hi, > > I am having a strange problem with a sqlite3 database. See the following > transcript: > > > sqlite3 kdb "select * from kfz where kfznr=48482364;" > > 48482364|48|0|0C|00|00|0||20|5B93|1746294314|||0|GP-T 1006|0 > > kfznr is the

[sqlite] Indexing problem

Hi, I am having a strange problem with a sqlite3 database. See the following transcript: > sqlite3 kdb "select * from kfz where kfznr=48482364;" > 48482364|48|0|0C|00|00|0||20|5B93|1746294314|||0|GP-T 1006|0 kfznr is the primary key, so this is to be expected. Now two queries as fired from

[sqlite] Indexing on UNION

Hello, i'm quite new to programmind with databases... I'll try to explain my Problem on a symplified example: I have Two Tables with large amount of data: CREATE TABLE "tabelle1" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "wert" INTEGER ) CREATE TABLE "tabelle2" ( "id" INTEGER

Re: [sqlite] Indexing virtual tables

Nobody? Did I make myself clear or do you need more (or maybe less!) explanations? Thanks, Aladdin > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Date: Sat, 17 May 2008 16:41:49 +0200 > Subject: [sqlite] Indexing virtual tables > > > Hi! Here is what I'm st

[sqlite] Indexing virtual tables

timal. What is the best strategy to achieve optimal speed and needed storage? Am I missing a trivial point? Thank you for any help on that! Aladdin > Date: Mon, 12 May 2008 15:37:22 -0700 > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Indexing virtual tables

Re: [sqlite] indexing rows from a query

On May 16, 2008, at 5:04 PM, Jay A. Kreibich wrote: > On Fri, May 16, 2008 at 04:44:10PM -0700, [EMAIL PROTECTED] scratched on > the wall: >> Sorry if this is a silly question - I don't have much experience with >> databases. >> >> Say I have a table with many (millions+) of rows and I have a

Re: [sqlite] indexing rows from a query

On Fri, May 16, 2008 at 04:44:10PM -0700, [EMAIL PROTECTED] scratched on the wall: > Sorry if this is a silly question - I don't have much experience with > databases. > > Say I have a table with many (millions+) of rows and I have a query: > > SELECT * FROM mytable WHERE some_condition ORDER

Re: [sqlite] indexing rows from a query

<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > My real question is if there is an efficient way to index the results > of such a query. In other words, I'm looking for rows N through N+100 > of the result. Can I do much better than just executing the query and > throwing away the

Re: [sqlite] indexing rows from a query

[EMAIL PROTECTED] wrote: > Sorry if this is a silly question - I don't have much experience with > databases. > > Say I have a table with many (millions+) of rows and I have a query: > > SELECT * FROM mytable WHERE some_condition ORDER BY rowid > > First, I'm assuming that in addition to

[sqlite] indexing rows from a query

Sorry if this is a silly question - I don't have much experience with databases. Say I have a table with many (millions+) of rows and I have a query: SELECT * FROM mytable WHERE some_condition ORDER BY rowid First, I'm assuming that in addition to whatever time some_condition takes, I'll

Re: [sqlite] Indexing virtual tables

I'm not quite clear on your question - why wouldn't you just create any indices you need within the virtual-table implementation itself? Sort of like how fts uses SQLite tables to implement data-storage for the full-text index. -scott On Mon, May 5, 2008 at 10:13 AM, Aladdin Lampé <[EMAIL

[sqlite] Indexing virtual tables

Just thinking again about indexing strategies on virtual tables, I'm wondering why virtual tables could not be indexed using the "normal" SQLite command "INDEX". Indeed, I just expected that the data inside the column of the virtual table could be sequentially scanned (using the "xColumn"

Re: [sqlite] Indexing and Search speed

From: Dennis Cote <[EMAIL PROTECTED]> > No, that's not true. A sub-query is like any other query. I have > rearranged the query to make it more readable. > > select types.Track,types.URL > from ALBUM > inner join (select * from MUSIC where Artist_Id =?) as types > on

Re: [sqlite] Indexing and Search speed

dcharno wrote: >> select types.Track,types.URL from ALBUM inner join (select * from MUSIC >> where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by >> ALBUM.YomiAlbumName ; > > How does the subquery work in this statement? I thought subqueries > could only retrieve a single column.

Re: [sqlite] Indexing and Search speed

> select types.Track,types.URL from ALBUM inner join (select * from MUSIC > where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by > ALBUM.YomiAlbumName ; How does the subquery work in this statement? I thought subqueries could only retrieve a single column.

[sqlite] Indexing and Search speed

Hi, I am having 4 records in my database. I am using Joins method. My Table Looks like: "PRAGMA encoding = UTF16;" "CREATE TABLE ALBUMARTIST (AlbumArtistId INTEGER PRIMARY KEY NOT NULL, AlbumArtistName TEXT NOT NULL COLLATE NOCASE, YomiAlbumArtistName TEXT NOT NULL, UNIQUE

Re: [sqlite] indexing

On Feb 13, 2008 1:01 PM, Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote: > Thank you for a clear and precise answer. > > I seem to recall that it is possible to have in-memory databases with > sqlite. But perhaps that is only possible with alot of tinkering and > using the C functions. Is that

Re: [sqlite] indexing

On Feb 13, 2008, at 11:12 AM, [EMAIL PROTECTED] wrote: > Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote: >> I have a table with two variables, say A and B (both integers). The >> table is rather large - around 2.9 GB on disk. Every combination of >> (A,B) occurs only once. I am creating a unique

Re: [sqlite] indexing

Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote: > I have a table with two variables, say A and B (both integers). The > table is rather large - around 2.9 GB on disk. Every combination of > (A,B) occurs only once. I am creating a unique index as >CREATE UNIQUE INDEX ABidx ON abtable (A,B)

[sqlite] indexing

I have a table with two variables, say A and B (both integers). The table is rather large - around 2.9 GB on disk. Every combination of (A,B) occurs only once. I am creating a unique index as CREATE UNIQUE INDEX ABidx ON abtable (A,B) It seems that the (A,B) index is created much slower

[sqlite] indexing BLOBs

folks, I have never worked with BLOBs, but am now going to. Feel a bit nervous. The way I understand it, our favorite db breaks a BLOB into its predetermined chunks (4096 bytes or whatever) and figures out how and where to store them. We just ask it to put in the BLOB or take out the BLOB, and

Re: [sqlite] indexing large databases

On May 10, 2007, at 11:08 PM, Juri Wichanow wrote: For "create index.." in large database : "pragma default_cache_size = 2000;" For "select ..." -- "pragma default_cache_size = 1200;" Hmm, quite interesting. I would like to share my naive observations, which led me to believe the

[sqlite] indexing large databases

For "create index.." in large database : "pragma default_cache_size = 2000;" For "select ..." -- "pragma default_cache_size = 1200;" Juri

Re: [sqlite] indexing large databases

Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote: > > Having said that, I can understand that sorting and disk cache and so > on factors in - but my initial database is already very large (1.3GB > - 145.000.000 milion rows), and surely that disk cache would already > factor in at that stage?

Re: [sqlite] indexing large databases

On May 10, 2007, at 3:04 PM, [EMAIL PROTECTED] wrote: Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote: Hi We are using SQLite for a fairly big (but simple) calculation, and have some problems when creating an index on a database with 600.000.000 rows. Specifically it has not ended even after

Re: [sqlite] indexing large databases

Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote: > Hi > > We are using SQLite for a fairly big (but simple) calculation, and > have some problems when creating an index on a database with > 600.000.000 rows. Specifically it has not ended even after 5 days of > running. We have done it

[sqlite] indexing large databases

Hi We are using SQLite for a fairly big (but simple) calculation, and have some problems when creating an index on a database with 600.000.000 rows. Specifically it has not ended even after 5 days of running. We have done it successfully on 25% of the full data base, and are now

Re: [sqlite] Indexing on multiple columns

Hi, Fascinating, thanks!! :-) Indeed this query does use the optimizer. :-) If I understand, by rewriting the query to have an AND statement at the top level and getting a simple comparison over to the left we can utilize the index. *cheers* Ben [EMAIL PROTECTED] wrote: SELECT name

[sqlite] Indexing on multiple columns

Hi Y'all, Is there a compact way (or is it even possible) to use multiple columns (that I have in my order-by clause) for an operator like > or >=? I have a database of airports, something like this: create table airports( id integer primary key, name varchar not null); create index

[sqlite] Indexing LIKE. Was: case insensitivity

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > Dixon Hutchinson > wrote: > > Sorry, meant to include a reference to 'p' in my select: > > SELECT * FROM foo WHERE bar LIKE 'something' AND p='some_int'; > > An expression involving LIKE cannot use an index anyway. This is mostly true, but there

Re: [sqlite] indexing large sqlite db

--- Andrew McCollum <[EMAIL PROTECTED]> wrote: > I am building a very large (> 500M rows, ~6 GB) sqlite database that has > three integer columns. I find that inserting all the rows takes only a > couple hours, but when I try to create an index on any of the columns the > process will run for

[sqlite] indexing large sqlite db

Hello, I am building a very large (> 500M rows, ~6 GB) sqlite database that has three integer columns. I find that inserting all the rows takes only a couple hours, but when I try to create an index on any of the columns the process will run for days without finishing. If the entire operation

Re: [sqlite] Indexing

On 2/7/06, chetana bhargav <[EMAIL PROTECTED]> wrote: > I am sure that this question would have been asked many times earlier also. > I am new to > this list, can any one point me about some info on indexing in SQLite. The > time efficiency > and space it requires. Some Do's and Dont's about

[sqlite] Indexing

Hi, I am sure that this question would have been asked many times earlier also. I am new to this list, can any one point me about some info on indexing in SQLite. The time efficiency and space it requires. Some Do's and Dont's about indexing. Thanks for the help in advance.

[sqlite] Indexing and Searching

Is it possible to have searchable data type with indexed access in sqlite? I am looking for something similar to tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/) TIA!

RE: [sqlite] Indexing problem

is a good sign. -Tom > -Original Message- > From: Ted Unangst [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 19, 2005 1:28 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Indexing problem > > Thomas Briggs wrote: > > >I think the common

Re: [sqlite] Indexing problem

On Tue, 2005-04-19 at 10:27 -0700, Ted Unangst wrote: > Two tables in a where can use two indices in sqlite? > Correct. SQLite (and every other SQL RDBMS that I know of) uses as many as but no more than one index per table in a join. If the same table is used more than once in a join, then

Re: [sqlite] Indexing problem

Thomas Briggs wrote: I think the common misconception is that indexes on multiple individual columns can be used in conjunction with one another, which isn't the case (unless you're talking about bitmap indexes, but since SQLite doesn't have those, we clearly aren't :P). Once you get

[sqlite] Indexing problem

Thank you for spelling out that only one index can be used per query - probably a basic principle for sqlite experts, but one that had passed me by! The following tuning guide mentions that "if there is a choice of indexes, the query optimizer may make a bad choice":

RE: [sqlite] Indexing problem

> I was puzzled that removing the single-column index on > Season actually enabled the original query to > complete, having read somewhere in the sqlite docs > that indexing columns used in WHERE conditions > improves performance. Is this something to do with the That's a true statement in

[sqlite] Indexing problem

table would make a huge difference in how this query will perform, I think. -Tom > -Original Message- > From: John Proudlove [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 14, 2005 10:26 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Indexing problem > > H

RE: [sqlite] Indexing problem

age- > From: John Proudlove [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 14, 2005 10:26 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Indexing problem > > Hello, > > Can anyone shed light on the following problem, > experienced with the SQLite

[sqlite] Indexing problem

Hello, Can anyone shed light on the following problem, experienced with the SQLite command line utility (v3.0.8) on Solaris/SPARC? The query below hangs (fails to complete within 5 minutes) using the indices shown, but after removing the index on the Season column (used in the WHERE condition),

Re: [sqlite] indexing large tables

Hi, this is the exact table definition: create table wordlinks ( id integer primary key, document integer, docword integer, count integer ) in fact i wouldn't need the primary key. i need an index on docword

Re: [sqlite] indexing large tables

Hi, well, i put most things in transactions, but in this case that wouldn't make a difference. the query was simply: create index idx_linktable on linktable (wid) that ran for hours and hours on the huge table until i killed it, or with benchmarks as described in previous post on the tables

Re: [sqlite] indexing large tables

... > now, i tried to put the index on the table afterwards. i tried this when > the table was real big > (600Mb, about 40.000.000 rows).. After six hours, sqlite had read and > wrote 150Gb (!) to disk > (that is: reading 150Gb, writing 150Gb according to windows taskman) the > job still wasn't

[sqlite] indexing large tables

Hi, i have the following problem: in my database is one specific table that gets quite a lot of inserts..the table consist only of a primary key and three integer values. I need to index, besides the primary table, 2 more columns on the table. there is no option to put a 'unique' index. if i