Re: [sqlite] Scan table in explain query. Question.

[Clemens Ladisch]

Staffan Tylen wrote:
> SEARCH USING COVERING INDEX
> SEARCH USING INDEX
> SCAN USING COVERING INDEX
>
> What is the difference and what do they imply?

Full table scans, index lookups (searches), and covering indices are
explained on this page: .


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Scan table in explain query. Question.

[Staffan Tylen]

Reading this thread I decided to run an explain query plan for a query that
I have with multiple selects combined with union. The (simplified) result
is:

SEARCH TABLE UB USING COVERING INDEX UBS (UBS=?) (~5108 rows)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BY
SEARCH TABLE UB USING COVERING INDEX UBA (UBA=?) (~5108 rows)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BY
COMPOUND SUBQUERIES 7 AND 8 (UNION)
SEARCH TABLE UB USING COVERING INDEX UBO (UBO=?) (~5108 rows)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BY
COMPOUND SUBQUERIES 6 AND 9 (UNION)
SEARCH TABLE UC USING INDEX UCS (UCS=?) (~10847 rows)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BY
COMPOUND SUBQUERIES 5 AND 10 (UNION)
SCAN TABLE UC (~10847 rows)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BY
COMPOUND SUBQUERIES 4 AND 11 (UNION)
SCAN TABLE UC (~10847 rows)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BY
COMPOUND SUBQUERIES 3 AND 12 (UNION)
SCAN TABLE UC (~10847 rows)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BY
COMPOUND SUBQUERIES 2 AND 13 (UNION)
SCAN TABLE UB USING COVERING INDEX UBW (~10215 rows)
COMPOUND SUBQUERIES 1 AND 14 (UNION)

I realise that there are 3 complete scans of table UC, which is not what I
would prefer to see. But what draws my attention are the three variations:

SEARCH USING COVERING INDEX
SEARCH USING INDEX
SCAN USING COVERING INDEX

What is the difference and what do they imply?

Thanks,
Staffan




On Sat, May 11, 2013 at 5:49 PM, Keith Medcalf  wrote:

>
> ANALYZE analyzes the data distribution of the indexes.   This is affected
> by the data distribution of the tables, since the indexes index the tables.
>  The statistics computed by the analysis are stored in the database and
> used to choose the most efficient way to execute a query.
>
> If your data never changes, then you never need to run analyze but the one
> time.  If the statistical result never changes, then you only need to run
> analyze one time.
>
> However, if it is possible that the data distribution could change, then
> you need to run analyze sto that the statistical result "matches" the
> actual database.
>
> For example if you have a table with 4 records in it and run analyze, this
> will be recorded.
> If you later insert 14 billion records in that table, then when you
> perform a query the optimizer will ACT AS IF the table only had four
> records. This may or may not result in a non-optimal query plan and
> non-optimal performance.
>
> Only you, the designer of the database, will know the answer to that
> question.
>
> The solution however is simple:  run analyze so that the statistical data
> matches the current content of the database.
>
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Paolo Bolzoni
> > Sent: Saturday, 11 May, 2013 09:17
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Scan table in explain query. Question.
> >
> > At the moment EXPLAIN gives me a very small number of rows
> > for all the queries (thanks to indexes) a part of this one, so if I
> > understand correctly ANALYZE is not needed anymore.
> >
> > Or I can get a speed up executing once in a while?
> >
> > Regards,
> > Paolo
> >
> >
> > On Sat, May 11, 2013 at 1:06 PM, Clemens Ladisch 
> > wrote:
> > > Paolo Bolzoni wrote:
> > >> sqlite> explain query plan SELECT id FROM tour LIMIT 1;
> > >> 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx
> > >> (~100 rows)
> > >>
> > >> I am not still sure I understand completely the output of explain
> > >> query plan.
> > >
> > > It means that SQLite estimates that the table (or its index) contains
> > > about 100 rows.
> > >
> > > The LIMIT clause is *not* used for this estimate.
> > >
> > >> To get this simple result, sqlite3 actually scans the whole
> > >> table?
> > >
> > > No.  Without the LIMIT, the query would scan the entire index.  With
> the
> > > LIMIT, the scan is stopped after the first row.
> > >
> > >> I just have a question about query written in the begin, in my
> > >> application I use it to know if a table is empty. I execute it
> > >> and just use sqlite3_step return value. Errors a part, if it is
> > >> SQLITE_DONE the table is empty, if it is SQLITE_ROW it is not.
> > >
> > > This is more a documentation than an optimization improvement, but if
> > > you are not interested in the value of any particular column, you
> should
> > > not request it in the first place; i.e., use something like:
> > >   SELECT 0 FROM tour LIMIT 1;
> > >
> > >
> > > Regards,
> > > Clemens
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > 

Re: [sqlite] Scan table in explain query. Question.

[Keith Medcalf]

ANALYZE analyzes the data distribution of the indexes.   This is affected by 
the data distribution of the tables, since the indexes index the tables.  The 
statistics computed by the analysis are stored in the database and used to 
choose the most efficient way to execute a query.

If your data never changes, then you never need to run analyze but the one 
time.  If the statistical result never changes, then you only need to run 
analyze one time.

However, if it is possible that the data distribution could change, then you 
need to run analyze sto that the statistical result "matches" the actual 
database.

For example if you have a table with 4 records in it and run analyze, this will 
be recorded.
If you later insert 14 billion records in that table, then when you perform a 
query the optimizer will ACT AS IF the table only had four records. This may or 
may not result in a non-optimal query plan and non-optimal performance.

Only you, the designer of the database, will know the answer to that question.

The solution however is simple:  run analyze so that the statistical data 
matches the current content of the database.


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Paolo Bolzoni
> Sent: Saturday, 11 May, 2013 09:17
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Scan table in explain query. Question.
> 
> At the moment EXPLAIN gives me a very small number of rows
> for all the queries (thanks to indexes) a part of this one, so if I
> understand correctly ANALYZE is not needed anymore.
> 
> Or I can get a speed up executing once in a while?
> 
> Regards,
> Paolo
> 
> 
> On Sat, May 11, 2013 at 1:06 PM, Clemens Ladisch 
> wrote:
> > Paolo Bolzoni wrote:
> >> sqlite> explain query plan SELECT id FROM tour LIMIT 1;
> >> 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx
> >> (~100 rows)
> >>
> >> I am not still sure I understand completely the output of explain
> >> query plan.
> >
> > It means that SQLite estimates that the table (or its index) contains
> > about 100 rows.
> >
> > The LIMIT clause is *not* used for this estimate.
> >
> >> To get this simple result, sqlite3 actually scans the whole
> >> table?
> >
> > No.  Without the LIMIT, the query would scan the entire index.  With the
> > LIMIT, the scan is stopped after the first row.
> >
> >> I just have a question about query written in the begin, in my
> >> application I use it to know if a table is empty. I execute it
> >> and just use sqlite3_step return value. Errors a part, if it is
> >> SQLITE_DONE the table is empty, if it is SQLITE_ROW it is not.
> >
> > This is more a documentation than an optimization improvement, but if
> > you are not interested in the value of any particular column, you should
> > not request it in the first place; i.e., use something like:
> >   SELECT 0 FROM tour LIMIT 1;
> >
> >
> > Regards,
> > Clemens
> > ___
> > 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] Scan table in explain query. Question.

[Paolo Bolzoni]

At the moment EXPLAIN gives me a very small number of rows
for all the queries (thanks to indexes) a part of this one, so if I
understand correctly ANALYZE is not needed anymore.

Or I can get a speed up executing once in a while?

Regards,
Paolo


On Sat, May 11, 2013 at 1:06 PM, Clemens Ladisch  wrote:
> Paolo Bolzoni wrote:
>> sqlite> explain query plan SELECT id FROM tour LIMIT 1;
>> 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx
>> (~100 rows)
>>
>> I am not still sure I understand completely the output of explain
>> query plan.
>
> It means that SQLite estimates that the table (or its index) contains
> about 100 rows.
>
> The LIMIT clause is *not* used for this estimate.
>
>> To get this simple result, sqlite3 actually scans the whole
>> table?
>
> No.  Without the LIMIT, the query would scan the entire index.  With the
> LIMIT, the scan is stopped after the first row.
>
>> I just have a question about query written in the begin, in my
>> application I use it to know if a table is empty. I execute it
>> and just use sqlite3_step return value. Errors a part, if it is
>> SQLITE_DONE the table is empty, if it is SQLITE_ROW it is not.
>
> This is more a documentation than an optimization improvement, but if
> you are not interested in the value of any particular column, you should
> not request it in the first place; i.e., use something like:
>   SELECT 0 FROM tour LIMIT 1;
>
>
> Regards,
> Clemens
> ___
> 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] How do I optimize this?

[GB]

Igor Korot schrieb am 11.05.2013 08:14:

There is no WHERE filtering on positionsforleague table, so I'm not sure
what index to create.

What am I missing?

Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

You are actually filtering positionsforleague by id, namely the id of 
league named 'test'. So I'd recommend an index on positionsforleague.id. 
I did a short test and it also creates a different query plan if this 
index exists.


hth
Gerd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Scan table in explain query. Question.

[Clemens Ladisch]

Paolo Bolzoni wrote:
> sqlite> explain query plan SELECT id FROM tour LIMIT 1;
> 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx
> (~100 rows)
>
> I am not still sure I understand completely the output of explain
> query plan.

It means that SQLite estimates that the table (or its index) contains
about 100 rows.

The LIMIT clause is *not* used for this estimate.

> To get this simple result, sqlite3 actually scans the whole
> table?

No.  Without the LIMIT, the query would scan the entire index.  With the
LIMIT, the scan is stopped after the first row.

> I just have a question about query written in the begin, in my
> application I use it to know if a table is empty. I execute it
> and just use sqlite3_step return value. Errors a part, if it is
> SQLITE_DONE the table is empty, if it is SQLITE_ROW it is not.

This is more a documentation than an optimization improvement, but if
you are not interested in the value of any particular column, you should
not request it in the first place; i.e., use something like:
  SELECT 0 FROM tour LIMIT 1;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Scan table in explain query. Question.

[Simon Slavin]

On 11 May 2013, at 10:23am, Paolo Bolzoni  wrote:

> sqlite> explain query plan SELECT id FROM tour LIMIT 1;
> 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx
>(~100 rows)
> 
> I am not still sure I understand completely the output of explain
> query plan. But for sure, a small number in the end sounds a good
> thing. In my application after working with indexes and removing
> the big numbers of rows I got a great speed up.
> 
> I just have a question about query written in the begin, in my
> application I use it to know if a table is empty. I execute it
> and just use sqlite3_step return value. Errors a part, if it is
> SQLITE_DONE the table is empty, if it is SQLITE_ROW it is not.
> 
> To get this simple result, sqlite3 actually scans the whole
> table? If it is so, can be avoided?

Your design is good.  Your query is an efficient way of finding out whether the 
table is empty or not.  Working from the top of your post ...

You might get a more accurate row-count in the EXPLAIN QUERY PLAN by using the 
ANALYZE command once. But that won't speed up your query.

The EXPLAIN QUERY PLAN output says that SQLite has found a covering index, and 
this means SQLite won't need to make a temporary index to execute this command. 
 So there won't be any need for SQLite to read every row of the table, it will 
just jump to the next row each time you use _step().  In other words, the 
command is working the way you want it to.

Once you have tested the result of _step() don't forget to _finalize() your 
query.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Scan table in explain query. Question.

[Paolo Bolzoni]

sqlite> explain query plan SELECT id FROM tour LIMIT 1;
0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx
(~100 rows)

I am not still sure I understand completely the output of explain
query plan. But for sure, a small number in the end sounds a good
thing. In my application after working with indexes and removing
the big numbers of rows I got a great speed up.

I just have a question about query written in the begin, in my
application I use it to know if a table is empty. I execute it
and just use sqlite3_step return value. Errors a part, if it is
SQLITE_DONE the table is empty, if it is SQLITE_ROW it is not.

To get this simple result, sqlite3 actually scans the whole
table? If it is so, can be avoided?

Thanks,
Paolo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I optimize this?

[Clemens Ladisch]

Igor Korot wrote:
> EXPLAIN QUERY PLAN
> SELECT positions.positionname, positionsforleague.value
> FROM positionsforleague, positions, leagues
> WHERE leagues.id = positionsforleague.id
> AND positions.positionid = positionsforleague.positionid
> AND leagues.name = "test";
>
> 0|0|0|SCAN TABLE positionsforleague (~100 rows)
> 0|1|1|SEARCH TABLE positions USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
> 0|2|2|SEARCH TABLE leagues USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

Are these rows estimates correct?  Did you run ANALYZE?

How many rows from each of the three tables end up in the actual result?

> There is no WHERE filtering on positionsforleague table, so I'm not sure
> what index to create.

When joining with nested loops (which is the only method used by SQLite),
the outermost table always uses SCAN TABLE because each record must be
checked.  The outermost table uses an index only if there is a filter on
some column, but in this query, the only table with such a filter is
leagues, and the query planner has decided to not use it as the
outermost table anyway.

You could force the join order by using CROSS JOIN like this
():

  SELECT ...
  FROM leagues CROSS JOIN positionsforleague CROSS JOIN positions
  WHERE ... AND leagues.name = "test"

which will use an index on name, if available, but this is not
necessarily faster; you have to measure it.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] B-Tree usage

[Igor Korot]

Thank you.
That clear things up.

On Fri, May 10, 2013 at 11:28 PM, Nico Williams wrote:

> On Sat, May 11, 2013 at 1:12 AM, Igor Korot  wrote:
> > On Fri, May 10, 2013 at 11:07 PM, Nico Williams  >wrote:
> >> It'd help if you posted a simplified schema and statements that are
> >> not planned correctly by SQLite3.
> >
> > I'm not saying that my statements are not planned correctly.
> > What I'm saying is that by reading the link above _I_ gather:
> >
> > Using index will eliminate the need of B-Tree.
> >
> > However, it simply not true. Or is it?
>
> SQLite3 only implements B-trees.  What that page says is that in some
> cases SQLite3 will create a temporary B-tree to help with sorting, and
> in other cases SQLite3 can take advantage of an existing index.  But
> the index will still be a B-tree, and it's not implied that it
> wouldn't be.
>
> Nico
> --
> ___
> 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] B-Tree usage

[Nico Williams]

On Sat, May 11, 2013 at 1:12 AM, Igor Korot  wrote:
> On Fri, May 10, 2013 at 11:07 PM, Nico Williams wrote:
>> It'd help if you posted a simplified schema and statements that are
>> not planned correctly by SQLite3.
>
> I'm not saying that my statements are not planned correctly.
> What I'm saying is that by reading the link above _I_ gather:
>
> Using index will eliminate the need of B-Tree.
>
> However, it simply not true. Or is it?

SQLite3 only implements B-trees.  What that page says is that in some
cases SQLite3 will create a temporary B-tree to help with sorting, and
in other cases SQLite3 can take advantage of an existing index.  But
the index will still be a B-tree, and it's not implied that it
wouldn't be.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How do I optimize this?

[Igor Korot]

Hi, ALL,

sqlite> EXPLAIN QUERY PLAN SELECT positions.positionname,
positionsforleague.val
ue FROM positionsforleague, positions, leagues WHERE leagues.id =
positionsforle
ague.id AND positions.positionid = positionsforleague.positionid AND
leagues.nam
e = "test";

0|0|0|SCAN TABLE positionsforleague (~100 rows)
0|1|1|SEARCH TABLE positions USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|2|2|SEARCH TABLE leagues USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

sqlite> .schema positionsforleague
CREATE TABLE positionsforleague(positionid integer,id integer, value
integer, fo
reign key(positionid) references positions(positionid),foreign key(id)
reference
s leagues(id));

There is no WHERE filtering on positionsforleague table, so I'm not sure
what index to create.

What am I missing?

Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] B-Tree usage

[Igor Korot]

Hi, Nico,

On Fri, May 10, 2013 at 11:07 PM, Nico Williams wrote:

> It'd help if you posted a simplified schema and statements that are
> not planned correctly by SQLite3.
>

I'm not saying that my statements are not planned correctly.
What I'm saying is that by reading the link above _I_ gather:

Using index will eliminate the need of B-Tree.

However, it simply not true. Or is it?

Thank you.


> Nico
> --
> ___
> 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] B-Tree usage

[Nico Williams]

It'd help if you posted a simplified schema and statements that are
not planned correctly by SQLite3.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] B-Tree usage

[Nico Williams]

SQLite3 only uses B-trees, for tables and indexes.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] B-Tree usage

[Igor Korot]

Hi, ALL,
Looking at http://www.sqlite.org/eqp.html#section_1_2 it would be nice to
mention in which case B-Tree is unavoidable.

Only one use case with B-Tree vs indexing is used but from reading first
paragraph it looks like indexing is B-Tree replacement everywhere.

I have a query with ORDER BY and creating an index did not help. EXPLAIN
QUERY PLAN still used B-TREE.

It might help other people in the same situation.

Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users