SQLite Forum

Odd, query taking too long
Define "too long".

In any case, for optimal performance you would need to have the appropriate indexes on Traffic (checkDate, name, flow) and also on People (name, nick), rewrite the query as a join, and then run analyze to give the Query Planner sufficient information to be able to optimize your query.

You will also have to decide on whether those indexes should be UNIQUE or not, and how you want to handle multiple matching rows (ie, by only asking for DISTINCT results, perhaps).

As written the query cannot be "optimized" because you have written it in such a way that the only possible freedom (choosing whether to put People or Traffic) in the outer loop is eliminated.

As written the only thing that will be of any use is for you to optimize the retrieval path by having an index on Traffic (checkData, name, flow) (though this may be created automatically if it does not exist) so that the correlated query can be done as quickly as possible.  Nothing else will help.

Having 7 different indexes on 6 different columns of 2 different tables is also not helpful when doing table lookups that require matching multiple columns simultaneously.

At the CLI prompt you can use the .expert command to get recommendations on indexes.

Similarly, the .eqp command is useful for having the query plan displayed so you can see what is going on, without having to prefix the query with EXPLAIN QUERY PLAN.

As an aside, text constants use single quotes.  Double quotes are for identifiers.  SQLite3 will allow the use of double-quotes but if what is contained in them is a valid identifier in the context in which it is used, then it will be a reference to the identifier and not a text constant.  (leading to whining about queries like `SELECT * FROM Person WHERE name="name"` returning  all rows instead of just the one wished for).

The optimizer (query planner) is not magical.  It can only optimize within the constraints that you have declared using information that you have make available to it.  It does not (in most cases) re-write the query you have given to a more optimal form in order to allow what may be a more optimal generic solution.  Query re-writing is a very complex affair and provable correctness is a very difficult and complex problem.  There are various heavyweight products which can do very complex query re-writes and optimization analysis -- SQLite3 is not one of them -- it is for the most part constrained to "do what it is told" in the most efficient manner possible rather than "do what it thinks you meant even though that looks nothing like what you told it" as efficiently as possible.