If you really want to filter as you have specified, then it will take a while.
This is because the entire query must be performed (up to the point you are
applying the order), then sorted, then the top rows selected to match you
limit, then any remaining outer joins performed.
Or you can order by the "other side" of your join constraint (bm_ppal.nbmCalle)
but then you are not guaranteed to get results with no match first (since NULL
sorts before any value). You can do this by putting an exists in the where
clause to select records that DO NOT match the join constraint -- ie, where not
exists) (for your limit of records), UNION with those that do (ie, an equijoin)
(for your limit of records), and then sort (order by) and limit the result.
This should only take about twice (or maybe three) times as long since you are
in effect running the same query twice with different criterion placed on the
outer table only. Some additional time will be used for the extra exists test
and for the final sort and limit. But you will not be generating a 750,000 row
table to sort and filter either -- you will only be generating one twice the
size of what you want then sorting and limiting it even more.
eg:
select a, b
from
(
select a, b
from x
left join y on x.a = y.a
where not exists (select 1 from y where a = x.a)
order by x.a
limit 57
)
UNION
(
select a, b
from x
join y on x.a = y.a
order by x.a
limit 57
)
order by b
limit 57;
> move the join to immediately follow the FROM clause
> remove the word "left"
>
> > -----Original Message-----
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of MONSTRUO Hugo González
> > Sent: Monday, 27 March, 2017 09:35
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: [sqlite] change ORDER BY slowly
> >
> > Hi,
> >
> > I have a phone book (bm_ppal), 726.000 rows, 10 columns
> >
> > This phone book have this columns
> > Name Declared Type Type Size
> > nbmId INTEGER INTEGER
> > nbmCodigo VARCHAR (6) VARCHAR 6
> > abmNombre VARCHAR (320) VARCHAR 320
> > abmNombrePlano VARCHAR (320) VARCHAR 320
> > nbmCiudad INTEGER INTEGER
> > nbmTelefono VARCHAR (9) VARCHAR 9
> > nbmCalle INTEGER INTEGER
> > nbmNroPuerta VARCHAR (5) VARCHAR
> > nbmLongitud VARCHAR (5) VARCHAR
> > nbmLatitud VARCHAR (5) VARCHAR
> >
> > I use c# winform datagridview virtualmode
> >
> > The file.sqlite is in: https://1drv.ms/u/s!Ar9mkZ1CPa2WhkUSJLxLI0WKV_TP
> >
> > I have this sqlite sentence for read the file, very fast: 157 ms
> >
> > SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> > bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> >
> > bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> > bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
> > FROM bm_ppal
> > LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
> > LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> > LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
> > ORDER BY bm_ppal.nbmId
> > LIMIT 512
> > OFFSET 0
> > 157ms
> >
> > I have this sqlite sentence for read the file, change the ORDER BY, very
> > fast: 135ms
> >
> > SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> > bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> >
> > bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> > bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
> > FROM bm_ppal
> > LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
> > LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> > LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
> > ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId
> > LIMIT 512
> > OFFSET 0
> > 135ms
> >
> > but when I change the ORDER BY with an attributt of other table is very
> > slowly: 5699 ms
> >
> > SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> > bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> >
> > bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> > bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
> > FROM bm_ppal
> > LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
> > LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> > LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
> > ORDER BY bm_calles.abmNombre
> > LIMIT 512
> > OFFSET 0
> > 5699ms ??
> >
> > I create the index with:
> >
> > CREATE INDEX IF NOT EXISTS idxbm_calles2 ON bm_calles(abmNombre);
> >
> > where I create an index in the table bm_ppal for bm_calles(abmNombre)
> with
> > the LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> >
> > How I can optimize it ?
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
