Hi,
I have a view 'transfer_history' which aggregates records from 3 tables
using UNION ALL. the aggregate is about 102k records.
I have a query:
SELECT transfer_date from transfer_history
WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
This returns three records and takes a couple of milliseconds - good.
But if I add an 'order by' clause, or an aggregate (max) on
transfer_date, the time goes up to > 300ms. The reason seems to be that
the query planner uses scans for all three sub-queries instead of using
indexes on the underlying tables.
With the basic query yhe QP says;
SEARCH TABLE transfer AS tr USING INDEX sqlite_autoindex_transfer_1
(regn_no=? AND transfer_date<?) (~1 rows)
SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1 (regn_no=?)
(~1 rows)
COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1 (regn_no=?)
(~1 rows)
COMPOUND SUBQUERIES 1 AND 4 (UNION ALL)
With the 'order by' clause the QP says:
SCAN TABLE transfer AS tr (~49043 rows)
SCAN TABLE sheep AS s (~51858 rows)
COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
SCAN TABLE sheep AS s (~25929 rows)
COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
SCAN SUBQUERY 1 AS t2 (~4227 rows)
USE TEMP B-TREE FOR ORDER BY
Seems to me it ought to be able to just sort the result of the first
plan. ATM it's an order of magnitude quicker at least to do the sort in
Python in the application.
Pete
--
Peter Hardman
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
