CODEWITHSUNDEEP
c#sqlsqlite
Nikki Locke
Nikki Locke

Reputation: 2941

SQLite "automatic index on MyTable(Id)" when Field is the primary key of table

I have a complex C# program that uses dynamically built queries to read from a SQLite database. I notice when I run the program under the debugger, I get lots of output like:

SQLite warning (284): automatic index on MyTable(Id)

I have looked at the schema for MyTable, and Id is specified as the primary key, like this:

CREATE TABLE MyTable (Id varchar(50) collate nocase primary key not null,
Name varchar(50) not null, 
(etc)

I thought SQLite made indexes for primary keys anyway, so why is it making another one?

Also, on a related note, I get a lot of automatic index warnings about sub-queries. For instance, on the query:

SELECT MyTable.Id, MyTable.Name, Amount
FROM MyTable
LEFT JOIN (SELECT ArrangementId, Amount, AgreementDate FROM SubTable 
JOIN Organisations ON Organisations.Id = SubTable.OrganisationId AND Organisations.Direction = 1
) AS MyJoin ON MyJoin.ArrangementId = MyTable.Id
ORDER BY Id

Where

MyTable has Id as the primary key
Organisations has Id as the primary key
SubTable has a unique index on ArrangementId, OrganisationId

EXPLAIN QUERY PLAN on the query yields:

1|0|0|SCAN TABLE SubTable
1|1|1|SEARCH TABLE Organisations USING INDEX sqlite_autoindex_Organisations_1 (Id=?)
0|0|0|SCAN TABLE Arrangements USING INDEX sqlite_autoindex_Arrangements_1
0|1|1|SEARCH SUBQUERY 1 AS MyJoin USING AUTOMATIC COVERING INDEX (ArrangementId=?)

I guess SQLite isn't clever enough to realise that the subquery does not need to go into a temporary table?

Is there any way of rewriting the query so a subquery is avoided?

Upvotes: 2

Views: 1274

Answers (2)

CL.
CL.

Reputation: 180070

A collate nocase column results in a collate nocase index. That index cannot be used if the lookup does not use the same collation. (Comparisons with that column use nocase by default, but this does not help when the comparison is against another column with a different collation.)

If this query is important, consider creating a second index with the correct collation.

In the second query, the database must evaluate the subquery using a temporary table because it is the right operand of a left outer join (rule 3).

You could try to rewrite the query as a series of simple joins, if you're sure that the meaning stays the same:

FROM MyTable
LEFT JOIN SubTable ON MyTable.Id = SubTable.ArrangementId
LEFT JOIN Organisations ON Organisations.Id = SubTable.OrganisationId
                       AND Organisations.Direction = 1

Upvotes: 2

seairth
seairth

Reputation: 2062

Try making the following changes:

  • Make ID an integer primary key. This will actually be an alias for the internal rowid, not a separate column.
  • Make your current ID (the varchar column) a separate column. Optionally, add a unique constraint, if that's important.
  • In your child tables, use the integer ID instead of the varchar column. Additionally, add a foreign key on the column.

A few notes:

  • Using an INTEGER PRIMARY KEY in MyTable and moving the varchar to a separate column with a unique constraint will make no difference to the size of MyTable. As I mention above, the primary key column will be an alias for internal rowid column, so you aren't really adding any new columns.
  • By switching the child tables to using the integer as the foreign key, this will result in a smaller table. Even with the addition of the foreign key constraint, your database will be smaller than it is now. This should also make your joins faster (for large datasets, anyhow).

Upvotes: 0

Related Questions