Hi all,
Sorry for the repeat of this post; I?ve not heard back from anyone, and was
hoping someone might have some idea.
To recap:
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
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. If I then run ANALYZE, it?s back to being slow.
The result of .fullschema is as follows:
CREATE TABLE Txn (Txn_ID integer NOT NULL PRIMARY KEY, Txn_UUID varchar NOT
NULL DEFAULT '', Txn_Modified integer NOT NULL DEFAULT 0, Txn_Created timestamp
NOT NULL DEFAULT '', Txn_Type integer NOT NULL DEFAULT 0, Txn_Type_m integer
NOT NULL DEFAULT 0, Txn_ContactUUID varchar NOT NULL DEFAULT '',
Txn_ContactUUID_m integer NOT NULL DEFAULT 0, Txn_NameFull varchar NOT NULL
DEFAULT '' COLLATE NOCASE, Txn_NameFull_m integer NOT NULL DEFAULT 0,
Txn_Company varchar NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Company_m integer
NOT NULL DEFAULT 0, Txn_Address1 varchar NOT NULL DEFAULT '' COLLATE NOCASE,
Txn_Address1_m integer NOT NULL DEFAULT 0, Txn_Address2 varchar NOT NULL
DEFAULT '' COLLATE NOCASE, Txn_Address2_m integer NOT NULL DEFAULT 0,
Txn_Address3 varchar NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Address3_m integer
NOT NULL DEFAULT 0, Txn_City varchar NOT NULL DEFAULT '' COLLATE NOCASE,
Txn_City_m integer NOT NULL DEFAULT 0, Txn_County varchar NOT NULL DEFAULT ''
COLLATE NOCASE, Txn_County_m integer NOT NULL DEFAULT 0, Txn_Postcode varchar
NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Postcode_m integer NOT NULL DEFAULT 0,
Txn_Country varchar NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Country_m integer
NOT NULL DEFAULT 0, Txn_Date date NOT NULL DEFAULT '', Txn_Date_m integer NOT
NULL DEFAULT 0, Txn_Amount currency NOT NULL DEFAULT 0, Txn_Amount_m integer
NOT NULL DEFAULT 0, Txn_Method varchar NOT NULL DEFAULT '' COLLATE NOCASE,
Txn_Method_m integer NOT NULL DEFAULT 0, Txn_Reference integer NOT NULL DEFAULT
0, Txn_Reference_m integer NOT NULL DEFAULT 0, Txn_PaymentReference varchar NOT
NULL DEFAULT '' COLLATE NOCASE, Txn_PaymentReference_m integer NOT NULL DEFAULT
0, Txn_PaidInDate date NOT NULL DEFAULT '', Txn_PaidInDate_m integer NOT NULL
DEFAULT 0, Txn_VATReportDate date NOT NULL DEFAULT '', Txn_VATReportDate_m
integer NOT NULL DEFAULT 0, Txn_Notes varchar NOT NULL DEFAULT '' COLLATE
NOCASE, Txn_Notes_m integer NOT NULL DEFAULT 0);
CREATE INDEX idx_Txn_UUID ON Txn (Txn_UUID);
CREATE INDEX idx_Txn_ContactUUID ON Txn (Txn_ContactUUID);
CREATE INDEX idx_Txn_Reference ON Txn (Txn_Reference);
CREATE TABLE Appointment (Appointment_ID integer NOT NULL PRIMARY KEY,
Appointment_UUID varchar NOT NULL DEFAULT '', Appointment_Modified integer NOT
NULL DEFAULT 0, Appointment_Created timestamp NOT NULL DEFAULT '',
Appointment_AppointmentTypeUUID varchar NOT NULL DEFAULT '',
Appointment_AppointmentTypeUUID_m integer NOT NULL DEFAULT 0,
Appointment_StartDate date NOT NULL DEFAULT '', Appointment_StartDate_m integer
NOT NULL DEFAULT 0, Appointment_StartTime timestamp NOT NULL DEFAULT '',
Appointment_StartTime_m integer NOT NULL DEFAULT 0, Appointment_EndTime
timestamp NOT NULL DEFAULT '', Appointment_EndTime_m integer NOT NULL DEFAULT
0, Appointment_AddressUUID varchar NOT NULL DEFAULT '',
Appointment_AddressUUID_m integer NOT NULL DEFAULT 0, Appointment_Description
varchar NOT NULL DEFAULT '' COLLATE NOCASE, Appointment_Description_m integer
NOT NULL DEFAULT 0, Appointment_ShootUUID varchar NOT NULL DEFAULT '',
Appointment_ShootUUID_m integer NOT NULL DEFAULT 0, Appointment_SessionUUID
varchar NOT NULL DEFAULT '', Appointment_SessionUUID_m integer NOT NULL DEFAULT
0, Appointment_SessionStartOrEnd varchar NOT NULL DEFAULT '' COLLATE NOCASE,
Appointment_SessionStartOrEnd_m integer NOT NULL DEFAULT 0,
Appointment_ContactUUID varchar NOT NULL DEFAULT '', Appointment_ContactUUID_m
integer NOT NULL DEFAULT 0, Appointment_SaleUUID varchar NOT NULL DEFAULT '',
Appointment_SaleUUID_m integer NOT NULL DEFAULT 0, Appointment_QuoteUUID
varchar NOT NULL DEFAULT '', Appointment_QuoteUUID_m integer NOT NULL DEFAULT
0, Appointment_PurchaseUUID varchar NOT NULL DEFAULT '',
Appointment_PurchaseUUID_m integer NOT NULL DEFAULT 0, Appointment_TxnUUID
varchar NOT NULL DEFAULT '', Appointment_TxnUUID_m integer NOT NULL DEFAULT 0,
Appointment_ResourceUUID varchar NOT NULL DEFAULT '',
Appointment_ResourceUUID_m integer NOT NULL DEFAULT 0, Appointment_iCalUID
varchar NOT NULL DEFAULT '' COLLATE NOCASE, Appointment_iCalUID_m integer NOT
NULL DEFAULT 0, Appointment_iCalMD5 varchar NOT NULL DEFAULT '' COLLATE NOCASE,
Appointment_iCalMD5_m integer NOT NULL DEFAULT 0, Appointment_SaleItemUUID
varchar NOT NULL DEFAULT '', Appointment_SaleItemUUID_m integer NOT NULL
DEFAULT 0, Appointment_GoogleCalendarUID varchar NOT NULL DEFAULT '' COLLATE
NOCASE, Appointment_GoogleCalendarUID_m integer NOT NULL DEFAULT 0);
CREATE INDEX idx_Appointment_UUID ON Appointment (Appointment_UUID);
CREATE INDEX idx_Appointment_AddressUUID ON Appointment
(Appointment_AddressUUID);
CREATE INDEX idx_Appointment_ShootUUID ON Appointment (Appointment_ShootUUID);
CREATE INDEX idx_Appointment_SessionUUID ON Appointment
(Appointment_SessionUUID);
CREATE INDEX idx_Appointment_ContactUUID ON Appointment
(Appointment_ContactUUID);
CREATE INDEX idx_Appointment_SaleUUID ON Appointment (Appointment_SaleUUID);
CREATE INDEX idx_Appointment_QuoteUUID ON Appointment (Appointment_QuoteUUID);
CREATE INDEX idx_Appointment_PurchaseUUID ON Appointment
(Appointment_PurchaseUUID);
CREATE INDEX idx_Appointment_ResourceUUID ON Appointment
(Appointment_ResourceUUID);
CREATE INDEX idx_Appointment_SaleItemUUID ON Appointment
(Appointment_SaleItemUUID);
);nCalc_GroupHeadPhone NOT NULL DEFAULT ''T '',
CREATE INDEX idx_TxnCalc_TxnUUID ON TxnCalc(TxnCalc_TxnUUID);
CREATE INDEX idx_TxnCalc_ContactUUID ON TxnCalc(TxnCalc_ContactUUID);
CREATE INDEX idx_appointment_txnuuid on appointment(Appointment_TxnUUID);
ANALYZE sqlite_master;
ANALYZE sqlite_master;
INSERT INTO sqlite_stat1 VALUES('TxnCalc','idx_TxnCalc_ContactUUID','3670 4');
INSERT INTO sqlite_stat1 VALUES('TxnCalc','idx_TxnCalc_TxnUUID','3670 1');
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_appointment_txnuuid','1716
1716');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_SaleItemUUID','1716 1716');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_ResourceUUID','1716 1716');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_PurchaseUUID','1716 1716');
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_Appointment_QuoteUUID','1716
1716');
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_Appointment_SaleUUID','1716
1716');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_ContactUUID','1716 31');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_SessionUUID','1716 1716');
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_Appointment_ShootUUID','1716
2');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_AddressUUID','1716 429');
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_Appointment_UUID','1716 1');
INSERT INTO sqlite_stat1 VALUES('Txn','idx_Txn_Reference','3670 1');
INSERT INTO sqlite_stat1 VALUES('Txn','idx_Txn_ContactUUID','3670 4');
INSERT INTO sqlite_stat1 VALUES('Txn','idx_Txn_UUID','3670 1');
ANALYZE sqlite_master;
In the middle of all that, after the line
CREATE INDEX idx_Appointment_SaleItemUUID ON Appointment
(Appointment_SaleItemUUID);
is some slightly odd output - that's just as it appears in the command line
output in Terminal on my Mac. If you need to know what TxnCalc actually is,
then it's this:
CREATE TABLE TxnCalc (TxnCalc_TxnUUID VARCHAR, TxnCalc_UnallocatedSum currency,
TxnCalc_AllocatedSum currency, TxnCalc_Amount currency NOT NULL DEFAULT 0.00,
TxnCalc_Type NOT NULL DEFAULT '', TxnCalc_Date date NOT NULL DEFAULT '',
TxnCalc_Method NOT NULL DEFAULT '', TxnCalc_NameFull NOT NULL DEFAULT '',
TxnCalc_Company NOT NULL DEFAULT '', TxnCalc_ContactUUID NOT NULL DEFAULT '',
TxnCalc_Year NOT NULL DEFAULT '', TxnCalc_Month NOT NULL DEFAULT '',
TxnCalc_Email NOT NULL DEFAULT '', TxnCalc_Phone NOT NULL DEFAULT '',
TxnCalc_Website NOT NULL DEFAULT '', TxnCalc_GroupHeadEmail NOT NULL DEFAULT
'', TxnCalc_GroupHeadPhone NOT NULL DEFAULT '');
For the avoidance of doubt, the output above is from when the query is slow. If
I drop idx_appointment_txnuuid and recreate it, then the query is fast; the
line
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_appointment_txnuuid','1716
1716');
is then missing from the .fullschema output, but all the rest (including the
line which creates the index) is the same.
If I then run ANALYZE and run .fullschema again, the line
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_appointment_txnuuid','1716
1716');
has reappeared.
Hopefully someone might be able to shed some light on what?s going on here.
Thanks,
Hamish
