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)
