SQLite Database gives warning automatic index on <table_name>(column) After upgrading Android L

Question

I have upgraded my Nexus 7 with Android 5.0 Lollipop, Before that my application going well with SQLite Database but now Whenever I execute any type of query, It gives me log cat error like:

12-09 12:37:04.942: E/SQLiteLog(13041): (284) automatic index on area(server_id)
12-09 12:37:04.942: E/SQLiteLog(13041): (284) automatic index on account(area_id)
12-09 12:37:04.942: E/SQLiteLog(13041): (284) automatic index on staff_visit(account_id)
12-09 12:37:04.942: E/SQLiteLog(13041): (284) automatic index on ordertab(account_id)
12-09 12:37:04.960: E/SQLiteLog(13041): (284) automatic index on area(server_id)
12-09 12:37:04.960: E/SQLiteLog(13041): (284) automatic index on account(area_id)
12-09 12:37:04.960: E/SQLiteLog(13041): (284) automatic index on staff_visit(account_id)
12-09 12:37:04.960: E/SQLiteLog(13041): (284) automatic index on ordertab(account_id)
12-09 12:37:04.978: E/SQLiteLog(13041): (284) automatic index on area(server_id)
12-09 12:37:04.978: E/SQLiteLog(13041): (284) automatic index on account(area_id)
12-09 12:37:04.978: E/SQLiteLog(13041): (284) automatic index on staff_visit(account_id)
12-09 12:37:04.978: E/SQLiteLog(13041): (284) automatic index on ordertab(account_id)

So Is it Error of Any Lollipop Mistakes? I think so because i dont have updated my code before and after upgrading this OS.

Solution 1

Automatic indexing was introduced in sqlite 3.7.17. A version of sqlite with this feature was only included in Android L developer preview. This is why you get the message only on Lollipop but not earlier. Even if it is logged as an error, it's really just a message.

Basically, the automatic indexing comes into play when you're doing lookups on non-indexed columns. sqlite assumes there's so much data that generating a temporary index is cheaper than raw lookup.

Consider adding explicit, permanent indices for your lookup columns with CREATE INDEX. For example, after your CREATE TABLE:

CREATE INDEX indexname ON tablename(columnname);

where you can pick tablename(columnname) from the autoindex messages as produced by sqlite.

If you just want the old behavior back, you can disable auto-indexing with

PRAGMA automatic_index=off;

Solution 2

This was the top post while i was looking into this problem. Although i have a C# project and it might not be relevant for the OP, i thought it might still be helpfull for someone.

For those that wonder why the message keeps appearing, although an index was created explicitly; maybe your query is using a different collation.

I had a table with a text column and a select query with a where statement specifying where name = @var1 COLLATE NOCASE. This triggered the warnings, as the index i had created was default collation.

Thus, rewriting either the index, or the create table statement, to specify nocase for that column, made the warning disappear.

Comments

[Muhammad Babar]

BTW auto-index is good improves query performance time

[Muhammad Babar]

Its strange though as i have manually created indexes for my search or joins columns but still auto indexes are being created.

[Pratik Butani]

Thanks @laalto, If i do nothing then anything going to wrong?

[Pratik Butani]

So which is better way to index them. Is this problematic?

[laalto]

Add an index for each column you use in WHERE unless your workload is heavily insert-based

[Pratik Butani]

Thanks man. Accepted. Now I want to update it but i do not know where to write indexing and for which columns mainly. can you share me any example or help me?

[copolii]

I'm getting these messages on Views. The underlying tables do have an index on the mentioned columns, but I can't create an index on a view.

[Muhammad Babar]

So automatic indexing does reduce the query time and is helpful right?

[laalto]

@MuhammadBabar Depends e.g. on the amount of data and proportion of reads to writes.

[Muhammad Babar]

Are automatic indexes temporary?

[Muhammad Babar]

I have manually created index on foreign keys columns reference_num of my 5 tables after creating tables, but i'm still getting this log 02-11 23:49:35.588: E/SQLiteLog(20914): (284) automatic index on sqlite_sq_B76A2C00(reference_num) 02-11 23:49:35.588: E/SQLiteLog(20914): (284) automatic index on sqlite_sq_B76A2980(reference_num) 02-11 23:49:35.589: E/SQLiteLog(20914): (284) automatic index on sqlite_sq_B76A2700(reference_num) 02-11 23:49:35.589: E/SQLiteLog(20914): (284) automatic index on sqlite_sq_B76A2480(reference_num)

[Muhammad Babar]

in my query there are 5 joins with group by and aggreagte group_concat. It doesn't have any WHERE clause. Can't Index help on JOINS?

[AntonSack]

What I don't understand is why I keep getting these messages although I have created an index on exactly that table column....

[laalto]

@AntonSack Are you sure your index creation has actually been run?

[Raphael Royer-Rivard]

I hate it when developers can't understand the meaning of an error log... It stains my logcat output! It should be an Info log or Warning at most...

[ban-geoengineering]

@AntonSack I had the same problem as you. It was because I was creating my column as INTEGER rather than as INTEGER PRIMARY KEY. Give the latter a try, if appropriate.