Re: [sqlite] Index usage for order by and where clauses

On Wednesday, 29 October, 2014 07:47, Clemens Ladisch said: >Baruch Burstein wrote: >> If I have an index on table1(colA, colB), will it be used for both the >> where and the order by in either of these cases: >> select * from table1 where colA=1 order by colB; >> select * from table1 where

Re: [sqlite] Index usage for order by and where clauses

Baruch Burstein wrote: > If I have an index on table1(colA, colB), will it be used for both the > where and the order by in either of these cases: > > select * from table1 where colA=1 order by colB; > select * from table1 where colB=1 order by colA; $ sqlite3 sqlite> create table table1(colA,

Re: [sqlite] Index usage when using UNION

On 16 Dec 2011, at 2:20am, Igor Tandetnik wrote: > Simon Slavin wrote: >> On 15 Dec 2011, at 7:19pm, Alexandr Němec wrote: >> >>> [UNION] >> >> Your 'ORDER BY' clause applies only to the second SELECT. > > Not true. Yeah, so I noticed from Richard's post. Sorry

Re: [sqlite] Index usage when using UNION

Simon Slavin wrote: > On 15 Dec 2011, at 7:19pm, Alexandr Němec wrote: > >> just a quick question, I did not find the answer in the various technical >> documents. I have two identical tables with a id >> INTEGER as a primary key, which means that SELECTions ORDERed BY id

Re: [sqlite] Index usage when using UNION

2011/12/15 Alexandr Němec > > Dear all, > > just a quick question, I did not find the answer in the various technical > documents. I have two identical tables with a id INTEGER as a primary key, > which means that SELECTions ORDERed BY id are very fast. Now if I do SELECT > *

Re: [sqlite] Index usage when using UNION

On 15 Dec 2011, at 7:19pm, Alexandr Němec wrote: > just a quick question, I did not find the answer in the various technical > documents. I have two identical tables with a id INTEGER as a primary key, > which means that SELECTions ORDERed BY id are very fast. Now if I do SELECT * > FROM

Re: [sqlite] Index usage when querying views

Nikolaus Rath wrote: > However, if I use an intermediate view: > > sqlite>CREATE VIEW inode_blocks_v AS > SELECT * FROM inode_blocks > UNION > SELECT id as inode, 0 as blockno, block_id FROM inodes WHERE block_id > IS NOT NULL > > and then run the same

Re: [sqlite] Index usage when querying views

On 25 Sep 2011, at 9:25pm, Nikolaus Rath wrote: > However, if I use an intermediate view: > > sqlite>CREATE VIEW inode_blocks_v AS > SELECT * FROM inode_blocks > UNION > SELECT id as inode, 0 as blockno, block_id FROM inodes WHERE block_id > IS NOT NULL > > and then run

Re: [sqlite] Index usage

Matthew, Regarding: "There's no way to optimize your query to be fast in both situations." I do *not* know if this would be of any help, but the newest 3.1.18 sqlite release which includes the SQLITE_ENABLE_STAT2 feature may possibly be of interest: (and excuse me if you've mentioned this

Re: [sqlite] Index usage

On Mon, Sep 21, 2009 at 8:27 AM, Pavel Ivanov wrote: > > There's no way to optimize your query to be fast in both situations. > LIMIT clause is pretty hard to optimize. Maybe just to have a closer > look at the application structure - maybe it's not so necessary to do > ORDER

Re: [sqlite] Index usage

> My question: how can I optimize this kind of query so that it utilizes > both indexes, to grab the first [b] rows (ordered by time) which also > match [a]? Or am I just going to have to guess at which way will be > faster, and use "INDEXED BY" to force it? (The documentation says I > shouldn't

Re: [sqlite] Index usage with LIKE queries

<[EMAIL PROTECTED]> wrote: > I thought I can create two separate indexes: on name and on email, > and when I execute a query with "name LIKE 'value' OR email > LIKE 'value'" both indexes would be used. > If you are building an email indexing system, you problem want to use Full Text Search with

Re: [sqlite] Index usage with LIKE queries

<[EMAIL PROTECTED]> wrote: > IT> LIKE is case-insensitive by default. To have it use your index, you need > IT> to either make the index case-insensitive: > IT> > IT> CREATE INDEX test_name ON test (name COLLATE NOCASE); > > Sorry, tried to create the index this way, but it > still isn't used

Re: [sqlite] Index usage

At 14:25 20/06/2006, Mikey C wrote: Hi, I just wanted to ask for confirmation that my understanding on how the query optimiser works is correct. SQLite only uses one index for each table in a FROM? Yes What if tables are joined? Does an index get used for each joined table? No, just

Re: [sqlite] Index usage tracking

[EMAIL PROTECTED] wrote: > Dennis Jenkins <[EMAIL PROTECTED]> wrote: > >> I would like to know where the best place in sqlite is to patch to >> have it record (syslog for unix, OutputDebugString() for windows, >> nothing fancy) each time it decides to use an index to satisfy a query. >>

Re: [sqlite] Index usage tracking

Dennis Jenkins <[EMAIL PROTECTED]> wrote: > > I would like to know where the best place in sqlite is to patch to > have it record (syslog for unix, OutputDebugString() for windows, > nothing fancy) each time it decides to use an index to satisfy a query. The index decisions are all made in

Re: [sqlite] Index Usage

William Hachfeld wrote: Am I also correct in understanding that if I did: CREATE INDEX MultiColumnIndex ON Example (begin, end, grp); SELECT id FROM Example WHERE x < end AND y >= begin AND grp=g; That I would only make use of 1 of the 3 terms in the index? Correct. Specifically the

Re: [sqlite] Index Usage

Thanks for the information Richard. Your explanation, along with the "Virtual Database Engine" document that I was reading when you wrote this, makes things a lot more clear. After reading Ulrik's suggestions, I decided to poke around a little bit using "EXPLAIN" to see if I could discover what

Re: [sqlite] Index Usage

William Hachfeld wrote: Hi, Have a question for everyone regarding index usage in SQLite... Say that I have the following database schema: CREATE TABLE Example ( id INTEGER PRIMARY KEY, grp INTEGER, begin INTEGER, end INTEGER ); and I want to perform the

Re: [sqlite] Index Usage

Thanks for the advice Ulrik! I don't believe, however, that the alternate query you purposed using BETWEEN is quite equivalent to what I was going to do. I am storing intervals [begin, end) in the database and then looking for those intervals from the database that intersect [x, y) - not those

Re: [sqlite] Index Usage

, and then do BETWEEN 10 AND 14 I guess I should have made that clear. Cheers, Ulrik -Original Message- From: Ulrik Petersen [mailto:[EMAIL PROTECTED] Sent: Thursday, October 28, 2004 10:28 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Index Usage William, William Hachfeld wrote: Hi, Have

RE: [sqlite] Index Usage

I noticed that you use { instead of ( What do those do? -Original Message- From: Ulrik Petersen [mailto:[EMAIL PROTECTED] Sent: Thursday, October 28, 2004 10:28 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Index Usage William, William Hachfeld wrote: >Hi, > >Have a

Re: [sqlite] Index Usage

William, William Hachfeld wrote: Hi, Have a question for everyone regarding index usage in SQLite... Say that I have the following database schema: CREATE TABLE Example ( id INTEGER PRIMARY KEY, grp INTEGER, begin INTEGER, end INTEGER ); and I want to perform

RE: [sqlite] Index Usage

Great Question! I am eager to hear the response! I use a ton of JOINs and INTERSECTs. Coverage on that topic would be great too! -Original Message- From: William Hachfeld [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 27, 2004 9:34 AM To: [EMAIL PROTECTED] Subject: [sqlite] Index