SQLite Indexing?

Question

My app is using an external SQLite database. The database is created using DB Browser for SQLite software. I am using the following method to query my table with the column ENGLISH (same as en_word). However, problem is the query is slow when my database become large.

public static final String ENGLISH = "en_word";
public static final String TABLE_NAME = "words";

    String sql = "SELECT * FROM " + TABLE_NAME +
                    " WHERE " + ENGLISH + " LIKE ? ORDER BY LENGTH(" + ENGLISH + ") LIMIT 100";

            SQLiteDatabase db = initializer.getReadableDatabase();

            Cursor cursor = null;
            try {
                cursor = db.rawQuery(sql, new String[]{"%" + englishWord.trim() + "%"});

                List<Bean> wordList = new ArrayList<Bean>();
                while(cursor.moveToNext()) {
                    String english = cursor.getString(1);
                    String mal = cursor.getString(2);
                    wordList.add(new Bean(english, mal));
                }

                return wordList;
            } catch (SQLiteException exception) {
                exception.printStackTrace();
                return null;
            } finally {
                if (cursor != null)
                    cursor.close();
            }

I tried to create index using DB Browser for SQLite.

CREATE INDEX `kindx` ON `words` ( `en_word` )

However, do I need to modify my code so that my app will query the database using this index? If so, how to do that?

I do not have create table syntax. My database is created using DB Browser for SQLite and saved in asset folder — user2872856, Mar 15 '18 at 13:13
It is the same as `en_word`. I declared it as `public static final String ENGLISH = "en_word";` — user2872856, Mar 15 '18 at 13:16
Is your query slow when it's executed directly from DB Browser ? — Nicolas Cortell, Mar 15 '18 at 13:19
@NicolasCortell, you mean execute from DB Browser for SQLite on my laptop? Of course it is fast because computer processor is way faster than mobile phone. — user2872856, Mar 15 '18 at 13:25

score 0 · Answer 1 · answered Mar 15 '18 at 13:32

0

The problem is that SQLite, like most relational databases, can use an index when the parameter to a 'like' clause ends with a wildcard, it cannot use an index when the parameter begins with a wildcard.

So, for this type of query, the index will not be used, and you wind up with a full table scan. This is why it is slower with a large number of rows.

You are actually attempting to do what is known as "full text search", which is not really possible to do efficiently without database features to support it directly.

I have not tried it, but I see that SQLite does have full-text search capabilities, and that it is supported on Android. See Full text search example in Android for an example.

answered Mar 15 '18 at 13:32

GreyBeardedGeek

29,460
2
47
67

So if I change to `cursor = db.rawQuery(sql, new String[]{englishWord.trim() + "%"});` then index will be supported? – user2872856 Mar 15 '18 at 14:19
Yes, but that would also return only words where the prefix matches exactly. – GreyBeardedGeek Mar 15 '18 at 14:23
I only need change it to `cursor = db.rawQuery(sql, new String[]{englishWord.trim() + "%"});`? No other modification of my code needed? Mean the index is automatically supported? – user2872856 Mar 15 '18 at 14:29
Yes, that should work with no further modifications. – GreyBeardedGeek Mar 15 '18 at 14:53

SQLite Indexing?

1 Answers1