Explain results help...

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Explain results help...

jose isaias cabrera-3

Greetings!

The following query is taking a bit too long for my taste.  I did a quick query with explain and I got this...  Anything I can do to make it faster?  Yes, I know I need to do some major work on rearranging, but anything else?

sqlite> ATTACH 'L:\Data\OpenJobsTool\Selva.Shared.DB.UK.AllOpenProjs.db' AS client;
sqlite>
sqlite> explain
   ...> SELECT ls.id FROM LSOpenProjects ls JOIN client.LSOpenProjects cl ON
   ...> (
   ...>   cl.login != 'lsuser' AND
   ...>   cl.id = ls.id AND
   ...>   cl.XtraB != ls.XtraB
   ...> ) LIMIT 100;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     17    0                    00  Start at 17
1     Integer        100   1     0                    00  r[1]=100; LIMIT counter
2     OpenRead       2     403349  2     k(4,,,,)       00  root=403349 iDb=2; Proj_id_login_XtraB
3     OpenRead       0     5     2     47             00  root=5 iDb=2; LSOpenProjects
4     Rewind         2     16    2     0              00
5       Column         2     1     2                    00  r[2]=LSOpenProjects.login
6       Eq             3     15    2     (BINARY)       51  if r[2]==r[3] goto 1
5
7       IdxRowid       2     4     0                    00  r[4]=rowid
8       SeekRowid      0     15    4                    00  intkey=r[4]; pk
9       Column         2     2     5                    00  r[5]=LSOpenProjects.XtraB
10      Column         0     46    6                    00  r[6]=LSOpenProjects.XtraB
11      Eq             6     15    5     (BINARY)       51  if r[5]==r[6] goto 1
5
12      Copy           4     7     0                    00  r[7]=r[4]
13      ResultRow      7     1     0                    00  output=r[7]
14      DecrJumpZero   1     16    0                    00  if (--r[1])==0 goto
16
15    Next           2     5     0                    01
16    Halt           0     0     0                    00
17    Transaction    2     0     597   0              01  usesStmtJournal=0
18    String8        0     3     0     lsuser         00  r[3]='lsuser'
19    Goto           0     1     0                    00
sqlite>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Explain results help...

Simon Slavin-3


On 17 Aug 2017, at 4:36am, jose isaias cabrera <[hidden email]> wrote:

> The following query is taking a bit too long for my taste.  I did a quick query with explain and I got this...  Anything I can do to make it faster?  Yes, I know I need to do some major work on rearranging, but anything else?

Can you post again with just the SELECT command, and any indexes you have on either table ?

The solution is probably going to involve making an index which ideally suits the SELECT command.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Explain results help...

Jens Alfke-2
In reply to this post by jose isaias cabrera-3

> On Aug 16, 2017, at 8:36 PM, jose isaias cabrera <[hidden email]> wrote:
>
> The following query is taking a bit too long for my taste.  I did a quick query with explain and I got this…  

The output of EXPLAIN QUERY PLAN is a lot easier to understand; have you tried that? Most importantly it shows which indexes / tables are being searched (fast) or scanned (slow), and if a covering index is used.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Explain results help...

jose isaias cabrera-3

Jens Alfke wrote...

> On Aug 16, 2017, at 8:36 PM, jose isaias cabrera <[hidden email]>
> wrote:
>
> The following query is taking a bit too long for my taste.  I did a quick
> query with explain and I got this…

>The output of EXPLAIN QUERY PLAN is a lot easier to understand; have you
>tried that? Most
> importantly it shows which indexes / tables are being searched (fast) or
> scanned (slow),
> and if a covering index is used.

sqlite> ATTACH 'L:\Data\OpenJobsTool\Selva.Shared.DB.UK.AllOpenProjs.db' AS
client;
sqlite>
sqlite> explain query plan
   ...> SELECT ls.id FROM LSOpenProjects ls JOIN client.LSOpenProjects cl ON
   ...> (
   ...>   cl.login != 'lsuser' AND
   ...>   cl.id = ls.id AND
   ...>   cl.XtraB != ls.XtraB
   ...> ) LIMIT 100;
0|0|1|SCAN TABLE LSOpenProjects AS cl USING COVERING INDEX
Proj_id_login_XtraB
0|1|0|SEARCH TABLE LSOpenProjects AS ls USING INTEGER PRIMARY KEY (rowid=?)
sqlite>

from what this means, I guess what I need to do is to re-arrange the
table...



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Explain results help...

Simon Slavin-3


> On 17 Aug 2017, at 1:40pm, jose isaias cabrera <[hidden email]> wrote:
>
> sqlite> explain query plan
>  ...> SELECT ls.id FROM LSOpenProjects ls JOIN client.LSOpenProjects cl ON
>  ...> (
>  ...>   cl.login != 'lsuser' AND
>  ...>   cl.id = ls.id AND
>  ...>   cl.XtraB != ls.XtraB
>  ...> ) LIMIT 100;
> 0|0|1|SCAN TABLE LSOpenProjects AS cl USING COVERING INDEX Proj_id_login_XtraB
> 0|1|0|SEARCH TABLE LSOpenProjects AS ls USING INTEGER PRIMARY KEY (rowid=?)
> sqlite>
>
> from what this means, I guess what I need to do is to re-arrange the table...

Nope.  Just create an appropriate index.  Try doing this

CREATE INDEX test1 ON client.LSOpenProjects(login,XtraB);
ANALYZE

Then try the EXPLAIN QUERY PLAN again.  Though your query is unusual and it’s possible that SQLite has already found a good way to execute your command.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Explain results help...

R Smith
In reply to this post by jose isaias cabrera-3

On 2017/08/17 2:40 PM, jose isaias cabrera wrote:

>
> Jens Alfke wrote...
>
>> On Aug 16, 2017, at 8:36 PM, jose isaias cabrera
>> <[hidden email]> wrote:
>>
>> The following query is taking a bit too long for my taste.  I did a
>> quick query with explain and I got this…
>
>> The output of EXPLAIN QUERY PLAN is a lot easier to understand; have
>> you tried that? Most
>> importantly it shows which indexes / tables are being searched (fast)
>> or scanned (slow),
>> and if a covering index is used.
>
> sqlite> ATTACH
> 'L:\Data\OpenJobsTool\Selva.Shared.DB.UK.AllOpenProjs.db' AS client;
> sqlite>
> sqlite> explain query plan
>   ...> SELECT ls.id FROM LSOpenProjects ls JOIN client.LSOpenProjects
> cl ON
>   ...> (
>   ...>   cl.login != 'lsuser' AND
>   ...>   cl.id = ls.id AND
>   ...>   cl.XtraB != ls.XtraB
>   ...> ) LIMIT 100;
> 0|0|1|SCAN TABLE LSOpenProjects AS cl USING COVERING INDEX
> Proj_id_login_XtraB
> 0|1|0|SEARCH TABLE LSOpenProjects AS ls USING INTEGER PRIMARY KEY
> (rowid=?)
> sqlite>
>
> from what this means, I guess what I need to do is to re-arrange the
> table...

Perhaps not. That simply states that it's doing one scan and one lookup,
smartly picking the table with more restrictive filtering for the scan.
I can't see a query plan that would be any faster unless we don't have
all the info.

I don't know how many ID's in the client table will match that of the
main table, but I'm thinking they might both be primary keys so it will
be 1-to-1 and it will require one table-scan and one lookup no matter
which way you pose the question or order the table.

If there are multiple possible same ID's in the client table, things
change, and since you are only interested in an ID with matching
criteria in the attached DB, you could probably get faster results when
using an EXISTS check on the client DB in stead of the join, but if ID
is primary on the client too, then that's not viable. Also, if they are
both primary, a covering index that starts on a primary key is moot in
terms of lookup speed, but it might still offer improved read speed but
at a severe INSERT speed penalty.

How many records are in these two tables? What is "too long" for your
taste? It's hard to guess at solutions (or even possible problems)
without some specifics.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users