Re: [sqlite] Explain Query plan

COLLATE NOCASE has the desired effect, like then performs much the same as glob. pragma case_sensitve_like = had no effect. On Fri, Jun 8, 2018 at 9:29 PM, R Smith wrote: > > On 2018/06/08 1:24 PM, Clemens Ladisch wrote: >> >> David Burgess wrote: Have you run ANALYZE? >>> >>> Yes.

Re: [sqlite] Explain Query plan

On 2018/06/08 1:24 PM, Clemens Ladisch wrote: David Burgess wrote: Have you run ANALYZE? Yes. And LIKE now uses the index, but like is still ~100 times slower. A case-sensitive index cannot be used for a case-insensitive search. Create a COLLATE NOCASE index, or use GLOB. Or use pragma

Re: [sqlite] Explain Query plan

David Burgess wrote: >> Have you run ANALYZE? > > Yes. And LIKE now uses the index, but like is still ~100 times slower. A case-sensitive index cannot be used for a case-insensitive search. Create a COLLATE NOCASE index, or use GLOB. Regards, Clemens

[sqlite] Explain Query plan

I have a table with a 16 byte column (500,000 rows), the column is indexed. When I use explain query plan in the shell, with LIKE in the WHERE clause it responds with "SCAN TABLE" whereas GLOB (or EQUALS or >= ) use the index. LIKE 'exact hit', LIKE 'matches start%' or 'matches start _' returns

[sqlite] Explain query plan output and recursive CTE's

transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo (x); sqlite> explain query plan select x from foo union select x + 1 from foo; selectid|order|from|detail 1|0|0|SCAN TABLE foo 2|0|0|SCAN TABLE foo 0|0|0|COMPOUND SUBQUER

Re: [sqlite] explain query plan for 'where ID between ...'

xTom Byars wrote: > Returns > > SEARCH TABLE Tbl USING PRIMARY KEY (ID>? AND ID > Should that be (ID>=? AND ID<=?) explain query plan select * from t where x between 1 and 42; 0|0|0|SEARCH TABLE t USING PRIMARY KEY (x>? AND x= 1 and stops with > 42. The EQP output does not bother to always use

[sqlite] EXPLAIN QUERY PLAN for non-SELECT statements?

-Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von nomad at null.net Gesendet: Freitag, 19. Februar 2016 11:01 An: SQLite mailing list Betreff: [sqlite] EXPLAIN QUERY PLAN for non-SELECT

[sqlite] EXPLAIN QUERY PLAN for non-SELECT statements?

nomad at null.net wrote: > I wanted to see how SELECT queries within triggers are using indexes > (or not) which of course lead me to https://www.sqlite.org/eqp.html: > > EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may > also be appear with other statements that read data

[sqlite] EXPLAIN QUERY PLAN for non-SELECT statements?

I wanted to see how SELECT queries within triggers are using indexes (or not) which of course lead me to https://www.sqlite.org/eqp.html: EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may also be appear with other statements that read data from database tables (e.g.

[sqlite] `explain query plan`: does it have any side effects?

On Sat, 30 Jan 2016 19:07:21 + Simon Slavin wrote: > > On 30 Jan 2016, at 6:56pm, Yannick Duch?ne > wrote: > > > > That's strange, or may be it's SQLiteBrowser specific (I'm using it to test > > queries and get timings). > > Oh, you're not using your own code. > > Download the SQLite

[sqlite] `explain query plan`: does it have any side effects?

On Sat, 30 Jan 2016 19:03:12 +0100 Yannick Duch?ne wrote: > > On 30 Jan 2016, at 4:55pm, Yannick Duch?ne > > wrote: > > > > > Tweaking a query, I notice a query executed after I did an `explain query > > > plan `, executes faster than before it occured. > > > > Computer reads data from disk

[sqlite] `explain query plan`: does it have any side effects?

On 30 Jan 2016, at 6:56pm, Yannick Duch?ne wrote: > > That's strange, or may be it's SQLiteBrowser specific (I'm using it to test > queries and get timings). Oh, you're not using your own code. Download the SQLite command line tool and try the same queries. The shell tool does no caching

[sqlite] `explain query plan`: does it have any side effects?

On Sat, 30 Jan 2016 17:41:49 + Simon Slavin wrote: > > On 30 Jan 2016, at 4:55pm, Yannick Duch?ne > wrote: > > > Tweaking a query, I notice a query executed after I did an `explain query > > plan `, executes faster than before it occured. > > Computer reads data from disk when you do

[sqlite] `explain query plan`: does it have any side effects?

Funny real thing or illusion? Tweaking a query, I notice a query executed after I did an `explain query plan `, executes faster than before it occured. I checked multiple times in case it could be due to something else in the environment or due to since how long the SQLite connexion is opened,

[sqlite] `explain query plan`: does it have any side effects?

On 30 Jan 2016, at 4:55pm, Yannick Duch?ne wrote: > Tweaking a query, I notice a query executed after I did an `explain query > plan `, executes faster than before it occured. Computer reads data from disk when you do the "EXPLAIN QUERY PLAN". Data still in cache when you run the real

[sqlite] Explain query plan bug: overflow of subquery_id

As for version 3.8.6, when executing: sqlite> explain query plan with a as ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SEL

[sqlite] explain query plan showing SEARCH instead of SCAN for min/max operations

Isaac Goldberg wrote: > I'm seeing that EXPLAIN QUERY PLAN returns SEARCH instead of SCAN when > using min() or max(). For example: > > sqlite> create table t(a int, b int); > sqlite> explain query plan select min(b) from t; > 0|0|0|SEARCH TABLE t > > Why is t

[sqlite] explain query plan showing SEARCH instead of SCAN for min/max operations

On 2 Sep 2015, at 2:00pm, Simon Slavin wrote: > Because there's no convenient index. Whoops. Sorry. Ignore me. Pay attention to Clemens. Simon.

[sqlite] explain query plan showing SEARCH instead of SCAN for min/max operations

On 1 Sep 2015, at 7:40am, Isaac Goldberg wrote: > I'm seeing that EXPLAIN QUERY PLAN returns SEARCH instead of SCAN when > using min() or max(). For example: > > sqlite> create table t(a int, b int); >> sqlite> explain query plan select min(b) from t; >>

[sqlite] explain query plan showing SEARCH instead of SCAN for min/max operations

Hi all, I'm seeing that EXPLAIN QUERY PLAN returns SEARCH instead of SCAN when using min() or max(). For example: sqlite> create table t(a int, b int); > sqlite> explain query plan select min(b) from t; > 0|0|0|SEARCH TABLE t Why is this a SEARCH instead of a SCAN? Furthermore, is

Re: [sqlite] Explain Query Plan with virtual tables involved

nique Devienne [mailto:ddevie...@gmail.com] Gesendet: Montag, 17. Februar 2014 11:41 An: General Discussion of SQLite Database Betreff: [sqlite] Explain Query Plan with virtual tables involved Would it be possible to have plans for virtual tables more in line with plans for regular tables?

[sqlite] Explain Query Plan with virtual tables involved

ate index by_guid on folder_member(guid); sqlite> create index by_folder_type on folder_member(folder_type); sqlite> create index by_folder on folder_member(folder); sqlite> explain query plan select * from folder_member where guid='ab' and folder='bc'; 0|0|0|SEARCH TABLE folder_member USING IN

Re: [sqlite] EXPLAIN QUERY PLAN

reitag, 15. November 2013 18:08 An: General Discussion of SQLite Database Betreff: Re: [sqlite] EXPLAIN QUERY PLAN On 15 Nov 2013, at 4:29pm, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: Is there any place that explain each of this entries? <http://www.sqlite.org/opcode.html> H

Re: [sqlite] EXPLAIN QUERY PLAN

On 18 Nov 2013, at 7:46am, Hick Gunter wrote: > I use EXPLAIN to make sure that my virtual tables' xFilter methods get called > with the proper parameters. > > I guess I'm a geek ;) If you're writing virtual table code, then yes. Simon.

Re: [sqlite] EXPLAIN QUERY PLAN

"Simon Slavin" wrote... On 15 Nov 2013, at 4:29pm, jose isaias cabrera wrote: Is there any place that explain each of this entries? However, don't feel you should understand them. They're for geeks only. The output from

Re: [sqlite] EXPLAIN QUERY PLAN

On 15 Nov 2013, at 4:29pm, jose isaias cabrera wrote: > Is there any place that explain each of this entries? However, don't feel you should understand them. They're for geeks only. The output from EXPLAIN QUERY PLAN can be very

Re: [sqlite] EXPLAIN QUERY PLAN

"Jay A. Kreibich" wrote... On Wed, Nov 13, 2013 at 12:20:42AM +, Walter Hurry scratched on the wall: On Tue, 12 Nov 2013 17:47:05 -0500, jose isaias cabrera wrote: > I am trying to speed up our queries and normalize our DB and I am > reading, > > http://www.sqlite.org/eqp.html > > But, I

Re: [sqlite] EXPLAIN QUERY PLAN

On Wed, Nov 13, 2013 at 12:20:42AM +, Walter Hurry scratched on the wall: > On Tue, 12 Nov 2013 17:47:05 -0500, jose isaias cabrera wrote: > > > I am trying to speed up our queries and normalize our DB and I am > > reading, > > > > http://www.sqlite.org/eqp.html > > > > But, I am missing a

Re: [sqlite] EXPLAIN QUERY PLAN

On Tue, 12 Nov 2013 17:47:05 -0500, jose isaias cabrera wrote: > I am trying to speed up our queries and normalize our DB and I am > reading, > > http://www.sqlite.org/eqp.html > > But, I am missing a lot. Where do I read about the results and how to > make changes to the DB to speed

Re: [sqlite] EXPLAIN QUERY PLAN

On 12 Nov 2013, at 10:47pm, jose isaias cabrera wrote: > I am trying to speed up our queries and normalize our DB and I am reading, > > http://www.sqlite.org/eqp.html > > But, I am missing a lot. Where do I read about the results and how to make > changes to the DB to

[sqlite] EXPLAIN QUERY PLAN

Greetings and salutations. I am trying to speed up our queries and normalize our DB and I am reading, http://www.sqlite.org/eqp.html But, I am missing a lot. Where do I read about the results and how to make changes to the DB to speed up/enhance the DB response? Thanks. josé

Re: [sqlite] EXPLAIN QUERY PLAN

Thanks, Dan! On 11/30/10 9:05 AM, "Dan Kennedy" wrote: On 11/30/2010 11:38 PM, Duquette, William H (316H) wrote: > I've just discovered EXPLAIN QUERY PLAN; it looks quite useful, but one part > of the output is somewhat opaque. > > The command returns three columns:

Re: [sqlite] EXPLAIN QUERY PLAN

On 11/30/2010 11:38 PM, Duquette, William H (316H) wrote: > I've just discovered EXPLAIN QUERY PLAN; it looks quite useful, but one part > of the output is somewhat opaque. > > The command returns three columns: order, from, and detail. "order" is > evidently the order in which the indices are

[sqlite] EXPLAIN QUERY PLAN

I've just discovered EXPLAIN QUERY PLAN; it looks quite useful, but one part of the output is somewhat opaque. The command returns three columns: order, from, and detail. "order" is evidently the order in which the indices are applied; the "detail" explains which table and index is involved.

[sqlite] Explain Query Plan

Need some help understanding exactly what EXPLAIN QUERY PLAN is returning. I'm getting the following from the following. Can someone explain what the "order" "from" and "detail" should be telling me? Thanks, John Elrick Fenestra Techologies Scenario 1: Info on: query07 Execution time:

Re: [sqlite] explain query plan?

Petite Abeille wrote: > > what's the meaning of the first two numeral columns in > explain query plan? > The columns of the explain query plan output are labeled by SQLite as: sqlite> .header on sqlite> .mode column sqlite> explain query plan select * from t

[sqlite] explain query plan?

Hello, In "index using and explain using question", Dennis Cote wrote: "An EXPLAIN QUERY PLAN returns three columns. The output of explain query plan is not documented (to the best of my knowledge anyway), but is fairly self explanatory. It shows the order that tables are scanned and which

Re: [sqlite] Explain query plan

On 20-Dec-2007, at 3:02 PM, Kees Nuyt wrote: You will get much more detail with EXPLAIN SELECT ... It shows the VDBE code, which looks cryptic at first but will prove really informative. I'm still at the cryptic phase, but I'll figure it out. Thanks for confirming my suspicions about

Re: [sqlite] Explain query plan

On Thu, 20 Dec 2007 11:27:37 -0800, Steven Fisher <[EMAIL PROTECTED]> wrote: >So I've been using EXPLAIN QUERY PLAN to try to optimize my queries, >and I realized I'm missing something important. > >It shares what tables are used and what indexes, but as I understand >it, it doesn't include

[sqlite] Explain query plan

So I've been using EXPLAIN QUERY PLAN to try to optimize my queries, and I realized I'm missing something important. It shares what tables are used and what indexes, but as I understand it, it doesn't include whether I'm working entirely off indexes or not. For instance, if I have a line:

[sqlite] explain query plan/timing

Hello All, I've tried to find documentation/help about how to read output from EXPLAIN QUERY PLAN but without success. Can you point me where i can get it. And another question about timing/profiling for SQLite. Patch from this location