Black, Michael (IS) wrote, On 26/04/12 22:40:
I must be blind (something my wife would agree with)...but I did use "order by
transfer_date"....the "asc" making no difference.
Not at all. What you posted was the slow plan.
The three queries that show the planner's behaviour are:
1)
sqlite> explain query plan SELECT transfer_date FROM transfer_history WHERE regn_no = '023674' and transfer_date<=
'2012-05-01';
2|0|0|SEARCH TABLE transfer AS tr USING INDEX sqlite_autoindex_transfer_1
(regn_no=? AND transfer_date<?) (~1 rows)
2|1|1|SEARCH TABLE flock AS f USING INDEX sqlite_autoindex_flock_1 (flock_no=?)
(~1 rows)
3|0|0|SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1 (regn_no=?)
(~1 rows)
3|1|1|SEARCH TABLE flock AS f USING INDEX sqlite_autoindex_flock_1 (flock_no=?)
(~1 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
4|0|0|SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1 (regn_no=?)
(~1 rows)
4|1|1|SEARCH TABLE flock AS f USING INDEX sqlite_autoindex_flock_1 (flock_no=?)
(~1 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 4 (UNION ALL)
Uses indexes - good and fast.
2) Add an order by
sqlite> explain query plan SELECT transfer_date FROM transfer_history WHERE regn_no = '023674' and transfer_date<=
'2012-05-01' order by transfer_date asc;;
3|0|1|SCAN TABLE flock AS f (~161 rows)
3|1|0|SEARCH TABLE transfer AS tr USING INDEX tr_flock_no_index (flock_no=?)
(~5 rows)
4|0|1|SCAN TABLE flock AS f (~161 rows)
4|1|0|SEARCH TABLE sheep AS s USING INDEX sheep_org_flock_index
(originating_flock=?) (~5 rows)
2|0|0|COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
5|0|1|SCAN TABLE flock AS f (~161 rows)
5|1|0|SEARCH TABLE sheep AS s USING INDEX sheep_reg_flock_index
(registering_flock=?) (~2 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
0|0|0|SCAN SUBQUERY 1 (~67 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
Uses scans to assemble the result rows. Slow on larger tables.
3) Use Select *
sqlite> explain query plan SELECT * FROM transfer_history WHERE regn_no = '023674' and transfer_date<= '2012-05-01'
order by transfer_date asc;
2|0|0|SEARCH TABLE transfer AS tr USING INDEX sqlite_autoindex_transfer_1
(regn_no=? AND transfer_date<?) (~1 rows)
2|1|1|SEARCH TABLE flock AS f USING INDEX sqlite_autoindex_flock_1 (flock_no=?)
(~1 rows)
3|0|0|SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1 (regn_no=?)
(~1 rows)
3|1|1|SEARCH TABLE flock AS f USING INDEX sqlite_autoindex_flock_1 (flock_no=?)
(~1 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
4|0|0|SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1 (regn_no=?)
(~1 rows)
4|1|1|SEARCH TABLE flock AS f USING INDEX sqlite_autoindex_flock_1 (flock_no=?)
(~1 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 4 (UNION ALL)
sqlite>
Back to using indexes again.
The 'bug', if it can be called that, is that the planner sometimes chooses a
slower plan for no
reason that's obvious to the user.
Anyway, this is all history since Richard has now fixed this behaviour
(a49e909c87).
Pete
--
Peter Hardman
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
