2

I have a sqlite database and I use System.Data.SQLite in my C# project.

My request is :

SELECT d1.* FROM DYN_table as d1 INNER JOIN ( SELECT id_bill, ordre_phase, type, MAX(valeur) maximum FROM DYN_table   WHERE id_customer=347 AND type IN (1,5,2)  GROUP BY id_bill, ordre_phase, type) as d2 ON d1.type = d2.type AND d1.valeur = d2.maximum AND d1.ordre_phase=d2.ordre_phase AND d1.id_bill=d2.id_bill WHERE d1.id_customer=347 AND d1.type IN (1,5,2)

The request is working but I have the following Warning :

SQLite warning (284): automatic index on sqlite_sq_1E5A8EC0(id_bill)

When I try my request using "EXPLAIN QUERY PLAN" I see the last step is :

SEARCH SUBQUERY 1 AS d2 USING AUTOMATIC COVERING INDEX (id_bill=? AND ordre_phase=? AND type=?)

But there is an index for id_bill in this table, all the indexes I have : -Primary Key : id_player, id_bill, ordre_phase, type -type -id_player -ordre_phase -id_competition

I've tried to create an index with id_bill, ordre_phase and type but still the same "USING Automatic covering index" in the analyze.

Am I missing something ?

user2088807
  • 1,378
  • 2
  • 25
  • 47

2 Answers2

3

The database creates a temporary index on the column id_bill of the subquery d2.

It is not possible to create this index explicitly, unless you explicitly create a (temporary) table for this subquery.

Ignore the warning. If you need to optimize this query, consider rewriting it so that it is not necessary to do a join with such a complex subquery, i.e., so that subquery flattening is possible.

CL.
  • 173,858
  • 17
  • 217
  • 259
0

Expanding a bit on the answer by @CL., a Common Table Expression (SQLite WITH clause) may also create a temporary index, which the warning shows as cteName(tempColumnName)

DenverCR
  • 1,301
  • 1
  • 7
  • 6