SQLite query optimization (subquery and join)

Question

I have a table for a statistical project.

The structure looks like this:

CREATE TABLE NewStatHistory (
    StatHistoryID uniqueidentifier PRIMARY KEY NOT NULL,
    DateEntered dateTime NOT NULL,
    DateApplies dateTime NOT NULL,
    WhoEnteredID uniqueIdentifier NOT NULL,
    PostingID uniqueIdentifier NULL,
    EnteredValue decimal(19,5) NOT NULL,
    StatID uniqueIdentifier NOT NULL,
    StatStatus int NOT NULL,
    Notes varchar(500) NULL,
    CampusID uniqueidentifier NOT NULL,
    IsTarget bit NOT NULL DEFAULT 0
)

I need to output the last entered value for each DateApplies parameter.

This query is executed almost instantly in SqlServer, but in SQLite it just shuts down, and I cannot figure out how to optimize it.

SELECT NewStatHistory.* 
FROM NewStatHistory
INNER JOIN (
  SELECT MAX(DateEntered) entered, statID, DateApplies
  FROM NewStatHistory
  WHERE StatID = @statID 
    AND campusID = @campusID
    AND IsTarget = 0
  GROUP BY DateApplies, statID
) summary 
ON summary.entered = newstathistory.dateEntered AND
  summary.statID = newStatHistory.statID AND 
  summary.DateApplies = newStatHistory.DateApplies
WHERE NewStatHistory.StatID = @statID AND
  IsTarget = 0 AND
  campusID = @campusID
ORDER BY NewStatHistory.DateApplies DESC

Does anyone have any ideas on how to make it work. Otherwise, I will have to find another built-in db to use. In addition to this query, SQLite fulfilled everything I requested.

As for indexes, then on SqlServer I indexed only the primary key. In SQLite, I tried using only the primary key, but then added to several other columns to no avail.

SQLite , , , .

Answer 1

, , . . , .

Answer 2

EXPLAIN QUERY PLAN , 2 .

SQLite ,

- (, , ):

CREATE INDEX foo ON NewStatHistory(statID, campusID, IsTarget, DateApplies);