Fastest way to search through strings stored in sqlite database

A couple of things you can do...

• Change the return to a StringBuilder until the end.
• Only use a readable version of the database (that's probably not making much difference though)
• Do not get a new instance of the database every time, keep it opened until you don't need it anymore
• Query for only what you need with the "WHERE" argument in the SQL query.

See the code below with some changes:

// move this somewhere else in your Activity or suchSQLiteDatabase db = this.getReadableDatabase();public String searchKeyString(String key, String table){    StringBuilder rtn = new StringBuilder();    Log.d("searchKeyString",table);        // Select All Query        String selectQuery = "SELECT  * FROM " + table + " WHERE KEY_KEY=?";        Cursor cursor = db.rawQuery(selectQuery,  new String[] {key});        // you can change it to        // db.rawQuery("SELECT * FROM "+table+" WHERE KEY_KEY LIKE ?", new String[] {key+"%"});        // if you want to get everything starting with that key value        // looping through all rows and adding to list        if (cursor.moveToFirst()) {            do {                Log.d("searchKeyString","searching");                rtn.append(",").append(cursor.getString(2));            } while (cursor.moveToNext());        }        cursor.close();        Log.d("searchKeyString","finish search");    return rtn.toString();}

Note even if you want this to happen in "real-time" for the user, you will still need to move this to a separate Thread or ASyncTask or you are going to run into problems....

You should consider using SELECT * FROM your-table LIMIT 50, for example. And you can put two buttons "Back", "Next" on your view. If every page has max 50 items, the user is at page 1, and he taps "Next", then you can use this query:

SELECT * FROM your-table LIMIT 50 OFFSET 50

If your table contains most of text-data, and you want to integrate search deeply into your app, consider using virtual table with FTS.

Let sqlite do the hard lifting.

First off, add an index to the field you're searching for, if you don't have one already. Secondly, don't do a SELECT all with manual table scan, but rather use a query in the form

SELECT column_value  FROM my_table WHERE column_key LIKE "ABC%"

This returns the least amount of data, and the sql engine uses the index.