If you scroll down in my previous reply I put the explain query plan outputs in
with the queries. Guess I should have mentioned that. (Re-copied them below)
It was using the index on StyleId, thinking that was going to be faster. What
Dr Hipp suggested in adding the unary + operator does is turn that into an
expression rather than the raw field in the eyes of the planner, so it doesn't
use that index on StyleId.
"but the query is not in two tables.
its two select-where in the same table."
Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"?
That's joining two tables together. The fields you wanted returned may only be
from one of them, but it's still a join. The quick version(s) have Pois_bb as
the outer loop and Pois as the inner loop. The slowed down version had Pois as
the outer loop and Pois_bb as the inner loop.
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND y1 > -15.12862
AND x0 < 30.46203 AND x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id;
Run Time: real 0.109 user 0.000000 sys 0.000000
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND y1 > -15.12862
AND x0 < 30.46203 AND x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id
AND styleid IN (9,48,73,200,142,31,219);
Run Time: real 9.422 user 5.132433 sys 4.212027
selectid|order|from|detail
0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:
Forcing the order with CROSS JOIN
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND y1 >
-15.12862
AND x0 < 30.46203 AND x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id
AND styleid IN (9,48,73,200,142,31,219);
Run Time: real 0.078 user 0.000000 sys 0.000000
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1
With the unary + operator
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND y1 >
-15.12862
AND x0 < 30.46203 AND x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id
AND +styleid IN (9,48,73,200,142,31,219);
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
