2

I am trying to create a dictionary application on Android. I have a database of 80000 articles. When user enters a word in an EditText, I want to show suggestions in a ListView, To do that I use the following code:

    public Cursor query(String entry){
             String[] columns = new String[]{"_id", "word"};
             String[] selectionArgs = new String[]{entry + "%"};
             return mDB.query("word", columns, "word LIKE ?", selectionArgs, null, null, null);
    }

and I use SimpleCursorAdapter for the ListView.

The problem is that suggestions appear very late. I think the reason is LIKE in the SQL. I do not know any other way to do that. Is there anything I can do to boost the performance of getting the suggestions?

ipman
  • 1,212
  • 2
  • 21
  • 29
  • 1
    you should have a separate table with keywords for each article and make your searches there. otherwise it wont help. – Sergey Benner Feb 24 '12 at 11:56
  • @coderdem How many suggestions are planning to present? is it going to be a simple autocomplete kind of functionality? Or are really intending to show thousands of search results in the _suggestions_ result list? if not the latter, why not use _limit_? – Orkun Feb 24 '12 at 11:58
  • @SergeyBenner size of the word field is between 5 and 150 characters. – ipman Feb 24 '12 at 12:10
  • @Zortkun I want to show all possible suggestions. User should be able to scroll ListView down and see all possible words. – ipman Feb 24 '12 at 12:12

3 Answers3

4

You might find that adding an index on the word column helps a lot. See the documentation.

So you might try this, just after you create the table:

CREATE INDEX word_idx ON word (word);

(Note: I'm not sure if having the table and column both named word will cause syntax issues here. Try it and see!)

Graham Borland
  • 60,055
  • 21
  • 138
  • 179
  • wrong. the indexes should not be created mindlessly on text fields especially like articles and so on. – Sergey Benner Feb 24 '12 at 11:55
  • I presume the column contains individual words, not entire articles, as evidenced by the name `word`. – Graham Borland Feb 24 '12 at 11:56
  • @GrahamBorland word field is already sorted. However since it contains Turkish characters, it is sorted according to Turkish. I do not know whether this matters. – ipman Feb 24 '12 at 12:15
  • Even if the words are already sorted, SQLite doesn't know this. So the index is still a good idea. I've edited the answer. – Graham Borland Feb 24 '12 at 12:20
  • 2
    @coderdem if you're planning on using queries `like '%word%'` the index won't be used though. – Sergey Benner Feb 24 '12 at 13:04
  • i know once i tried to put index on my words (strings) column which contains 13000 records, and unfortunately it didn't have any benefit. So i would go with SergyBenner (+1 for you mate) – waqaslam Feb 24 '12 at 13:20
1

As a simple alternative, you can limit the suggestions in the arbitrary order with Limit as in this post: Using the LIMIT statement in a SQLite query

mDB.query("word", columns, "word LIKE ?", selectionArgs, null, null, null, "LIMIT 150" );

Since all the results are equally valid suggestions, the order will not matter.

Also you wonT be able to show crazy amount of suggestions anyway so you can simply use some fixed limit count depending on your UI. I gave 150 as an example.

Hope it helps..

Community
  • 1
  • 1
Orkun
  • 6,998
  • 8
  • 56
  • 103
1

Aside from the obvious index you should look into using full text search with MATCH rather than like if this is a dictionary. Android should support FTS3.

Check out http://www.sqlite.org/fts3.html and some answers here on SO regarding fts3 on Android.

It seems that the words should start with the string. Maybe this type of trick could help: SQLite FTS3 simulate LIKE somestring%

Community
  • 1
  • 1
Sebastian Olsson
  • 836
  • 6
  • 10