47

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.

Pratik Butani
  • 60,504
  • 58
  • 273
  • 437

2 Answers2

78

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;
Community
  • 1
  • 1
laalto
  • 150,114
  • 66
  • 286
  • 303
  • Thanks @laalto, If i do nothing then anything going to wrong? – Pratik Butani Dec 09 '14 at 09:41
  • So which is better way to index them. Is this problematic? – Pratik Butani Dec 09 '14 at 10:06
  • Add an index for each column you use in `WHERE` unless your workload is heavily insert-based – laalto Dec 09 '14 at 10:07
  • 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? – Pratik Butani Dec 09 '14 at 10:11
  • 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. – copolii Jan 12 '15 at 19:41
  • So automatic indexing does reduce the query time and is helpful right? – Muhammad Babar Feb 09 '15 at 09:40
  • 2
    @MuhammadBabar Depends e.g. on the amount of data and proportion of reads to writes. – laalto Feb 09 '15 at 09:58
  • Are automatic indexes temporary? – Muhammad Babar Feb 10 '15 at 20:35
  • 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 Feb 11 '15 at 18:50
  • 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? – Muhammad Babar Feb 11 '15 at 18:53
  • 3
    What I don't understand is why I keep getting these messages although I have created an index on exactly that table column.... – AntonSack Jun 05 '15 at 07:37
  • @AntonSack Are you sure your index creation has actually been run? – laalto Jun 05 '15 at 07:39
  • 5
    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... – Raphael Royer-Rivard Jul 10 '15 at 20:33
  • @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. – ban-geoengineering Oct 11 '16 at 10:25
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.

user1515791
  • 675
  • 4
  • 20