[sqlite] index usage and boolean constants

Hi fellow-sqliters, please forgive my newbie question, I hope it wasn't asked similar recently. I didn't find a way to properly search the list archives to check. CREATE TABLE demo ( id INTEGER PRIMARY KEY ); EXPLAIN QUERY PLAN SELECT * FROM demo WHERE 0 OR id=3; EXPLAIN QUERY PLAN SELECT *

[sqlite] index usage and boolean constants

On 10/28/15, Marcus Rohrmoser wrote: > > CREATE TABLE demo ( > id INTEGER PRIMARY KEY > ); > EXPLAIN QUERY PLAN SELECT * FROM demo WHERE 0 OR id=3; > EXPLAIN QUERY PLAN SELECT * FROM demo WHERE id=3; > > doesn't use the index in case 1, only in the 2nd case. > > Is there a way to hint the query

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,

[sqlite] Index usage for order by and where clauses

Hi, 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; -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

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

[sqlite] Index usage when using UNION

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 * FROM table1 UNION ALL SELECT * FROM table2 ORDER by id,

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

[sqlite] Index usage when querying views

Hello, I have two tables, inodes and inode_blocks, which have indices on id and inode respectively. The following query seems to make optimal use of the indices: sqlite> explain query plan SELECT 1 from inode_blocks where inode=42 UNION SELECT 1 from inodes where

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

[sqlite] Index usage

Hi, I'm trying to optimize a query for 2 different scenarios, and I'm having trouble getting something that works good in general. I want to be sure I'm not missing something. Here are the tables and indexes used in my database: sqlite> CREATE TABLE path_table (idx INTEGER PRIMARY KEY, path

Re[2]: [sqlite] Index usage with LIKE queries

dc> If you are building an email indexing system, you problem dc> want to use Full Text Search with fts3, not the LIKE dc> operator. See dc>http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex Thanks, interesting, but seems it's not my case, cause I'm doing search by partial name/email

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

[sqlite] Index usage with LIKE queries

Hi I'm trying to get index used with LIKE queries: CREATE TABLE test (name STRING); CREATE INDEX test_name ON test (name); EXPLAIN QUERY PLAN SELECT * FROM test WHERE name = 'aaa'; The output is: 0|0|TABLE test WITH INDEX test_name i.e. when LIKE is not used, the index is involved, everything is

[sqlite] Index usage for inequalities and GROUP BY

I have a table and indices as follows; CREATE TABLE employee(name TEXT,salary INTEGER,job TEXT); CREATE INDEX idx_emp_salary_job ON employee(salary,job); CREATE INDEX idx_emp_job ON employee(job); When I use the following query, SQLite seems to use idx_emp_salary_job for both WHERE and GROUP

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

[sqlite] Index usage

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? What if tables are joined? Does an index get used for each joined table? So if I have SELECT * FROM A INNER JOIN B ON A.COL1 =

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

[sqlite] Index usage tracking

Hello all, 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. For that matter, also each time is runs a select query and decided to not

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