[Simon Slavin]
> On 10 Feb 2012, at 3:24pm, Steinar Midtskogen wrote:
>
>> I feared that. As it is, it takes 6 seconds to do a SELECT * FROM
>> Combined LIMIT 1 ("Combined" is a view representing the merged table).
>> If I add an ORDER BY, it takes 35 seconds.
>>
>> Any way to speed up the ordering?
>
> Are you putting the ORDER BY in the VIEW definition or the SELECT definition
> ? Whichever you're doing, try the other one. Also, is there an index which
> provides a sorted list in an order which suits your ORDER BY clause ?
I created the view this way:
CREATE VIEW Combined AS
SELECT strftime("%Y-%m-%d %H:%M:%S", unix_time, "unixepoch") AS time, * FROM
(SELECT unix_time FROM Voksenlia1 UNION
SELECT unix_time FROM Voksenlia2 UNION
SELECT unix_time FROM Voksenlia3 UNION
SELECT unix_time FROM Voksenlia4 UNION
SELECT unix_time FROM Voksenlia5 UNION
SELECT unix_time FROM Voksenlia6 UNION
SELECT unix_time FROM Voksenlia8
)
LEFT NATURAL JOIN Voksenlia1
LEFT NATURAL JOIN Voksenlia2
LEFT NATURAL JOIN Voksenlia3
LEFT NATURAL JOIN Voksenlia4
LEFT NATURAL JOIN Voksenlia5
LEFT NATURAL JOIN Voksenlia6
LEFT NATURAL JOIN Voksenlia8 ORDER BY unix_time;
All 7 tables have a PRIMARY KEY (unix_time)
I tried then this:
CREATE VIEW Combined AS
SELECT strftime("%Y-%m-%d %H:%M:%S", unix_time, "unixepoch") AS time, * FROM
(SELECT unix_time FROM Voksenlia1 UNION
SELECT unix_time FROM Voksenlia2 UNION
SELECT unix_time FROM Voksenlia3 UNION
SELECT unix_time FROM Voksenlia4 UNION
SELECT unix_time FROM Voksenlia5 UNION
SELECT unix_time FROM Voksenlia6 UNION
SELECT unix_time FROM Voksenlia8 ORDER BY unix_time
)
LEFT NATURAL JOIN Voksenlia1
LEFT NATURAL JOIN Voksenlia2
LEFT NATURAL JOIN Voksenlia3
LEFT NATURAL JOIN Voksenlia4
LEFT NATURAL JOIN Voksenlia5
LEFT NATURAL JOIN Voksenlia6
LEFT NATURAL JOIN Voksenlia8;
And I got a big speedup. Is this what you meant?
The combined view currently has 84 columns and 2,548,717 rows, so
doing things the wrong way makes a huge impact.
--
Steinar
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
