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
<jic...@barrioinvi.net> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
