The main issue there I believe is that the order by/limit for the first two is on the outermost table, so it can order by/limit that right away. In the last query the order by/limit is on a table in the middle, so it can't order or filter on it right away, and needs the temp tree there.
I saw a small speed increase by putting the bm_calles join as the first one, doing the sort/limit after that 1 join, then doing the rest of the joins. But that was only 10% or so. So I'm not sure how best to optimize that. If they were inner joins then it could do it in any order it wanted, and thus be just as fast. But with the outer joins then I'm not sure. Also note that "unique primary key" is redundant and creates an extra unneeded index. Your first query (ORDER BY bm_ppal.nbmId LIMIT 512): selectid|order|from|detail 0|0|0|SCAN TABLE bm_ppal 0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?) 0|2|2|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?) 0|3|3|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?) Your second query (ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId LIMIT 512): selectid|order|from|detail 0|0|0|SCAN TABLE bm_ppal USING INDEX idxbm_ppal2 0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?) 0|2|2|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?) 0|3|3|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?) Your third (slow) query (ORDER BY bm_calles.abmNombre LIMIT 512): selectid|order|from|detail 0|0|0|SCAN TABLE bm_ppal 0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?) 0|2|2|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?) 0|3|3|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|USE TEMP B-TREE FOR ORDER BY (Not very helpful) Reordering (To make use of the limit as soon as possible): selectid|order|from|detail 1|0|0|SCAN TABLE bm_ppal 1|1|1|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?) 1|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|SCAN SUBQUERY 1 AS tmp 0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?) 0|2|2|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?) -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of MONSTRUO Hugo González Sent: Monday, March 27, 2017 11:35 AM 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
