Hi there, A follow up to this. If I run ANALYZE on the ?fast? version of the database - ie *after* I?ve recreated the index - performance drops back to the original slow speed.
Hamish > On 10 Apr 2015, at 14:24, Hamish Symington <info at hamishsymington.com> > wrote: > > Hello, > > I have a curiously slow query, and I?m not sure if it?s caused by something > I?m doing (most likely) or by something odd in sqlite (almost certainly not). > > The tables we have are: > Appointment(Appointment_UUID, Appointment_TxnUUID, Appointment_StartDate, and > other columns) > Txn(Txn_UUID, Txn_Created, Txn_Reference amongst others). > TxnCalc(TxnCalc_TxnUUID, TxnCalc_Date amongst others). > > The query I am running is this (using a prepared statement): > > SELECT TxnCalc_TxnUUID AS Txn_UUID > FROM ( > SELECT Txn.Txn_UUID AS TempTxn_UUID > FROM Txn > LEFT JOIN Appointment ON Appointment_TxnUUID=Txn_UUID > WHERE ( date( Appointment_StartDate ) >= trim( ? ) AND date( > Appointment_StartDate ) <= trim( ? ) ) ) > AS SubQueryResult, > TxnCalc, > Txn > WHERE TxnCalc_TxnUUID=TempTxn_UUID > AND TxnCalc_TxnUUID=Txn_UUID > GROUP BY TxnCalc_TxnUUID > ORDER BY TxnCalc_Date DESC, > Txn_Reference DESC, > Txn_Created DESC > > If possible, I?d like you to ignore the structure of the query, which isn?t > quite as good as it could be, I know; it?s the next bit that?s curious. > > We have indexes on Txn_UUID, TxnCalc_TxnUUID, Appointment_UUID - nothing > controversial there - and also on Appointment_TxnUUID. That field has many, > many blank values (as Appointments are most often linked to other things) and > only a very few non-blank values. They?re blank (ie ??) and not NULL, if that > makes a difference. > > When running that query using SQLite 3.7.14.1, it takes around 230ms to run. > The query plan is this: > SCAN TABLE Txn (~3670 rows) > SEARCH TABLE Appointment USING AUTOMATIC COVERING INDEX > (Appointment_TxnUUID=?) (~5 rows) > SEARCH TABLE TxnCalc USING INDEX idx_TxnCalc_TxnUUID (TxnCalc_TxnUUID=?) (~1 > rows) > SEARCH TABLE Txn USING INDEX idx_Txn_UUID (Txn_UUID=?) (~1 rows) > USE TEMP B-TREE FOR GROUP BY > USE TEMP B-TREE FOR ORDER BY > Interestingly, it?s not using the index which we created for the SEARCH TABLE > Appointment. > > When running the query using SQLite 3.8.8, it takes around 1600ms to run. The > query plan is this: > SCAN TABLE Txn USING COVERING INDEX idx_Txn_UUID > SCAN TABLE Appointment > SEARCH TABLE TxnCalc USING INDEX idx_TxnCalc_TxnUUID (TxnCalc_TxnUUID=?) > SEARCH TABLE Txn USING INDEX idx_Txn_UUID (Txn_UUID=?) > USE TEMP B-TREE FOR GROUP BY > USE TEMP B-TREE FOR ORDER BY > ie no index on Appointment at all. > > Now for the curious bit. If I drop the index which I made, and recreate it > using the exact same code with which I created it when I made my database - > CREATE INDEX idx_Appointment_TxnUUID ON Appointment(Appointment_TxnUUID) - > using sqlite 3.7.14.1 the query is down to 12ms, and using 3.8.8, it?s down > to 4ms. > > With the database in its ?slow? state, running ANALYZE makes no difference. > Likewise, REINDEX idx_Appointment_TxnUUID makes no difference. > > Background: I create the database, make the indexes, and then do a bunch of > INSERTs into it, along with UPDATES, DELETES etc. - basically using it as a > normal workhorse database. > > So, the question, after this somewhat long explanation: should deleting and > recreating an index like this cause such a massive performance improvement? > If so, why? What am I doing wrong in the first place? > > Thanks in advance for your assistance, > > Hamish > > -- > Hamish Symington > 07815 081282 : info at hamishsymington.com > > I also set custom cryptic crosswords: www.customcrypticcrosswords.com > (@CustomCryptics on Twitter) > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Hamish Symington 07815 081282 : info at hamishsymington.com I also set custom cryptic crosswords: www.customcrypticcrosswords.com (@CustomCryptics on Twitter)
