How to search in android sqlite database with speedy way?

Question

i tried to search in the sqlite database using the below code :

    Cursor cusror;
     cursor=db.rawQuery("SELECT * FROM "+ Contactsnew.TABLE02 + " WHERE " 
            + Contactsnew.userid + " = " + Contactsnew.userId + " AND " +   
      Contactsnew.TITLE +
             " LIKE  '"+search.getText()+"%'");

its working successfully , but in huge database its working slowly. i searched the last days to find third library to work with my own database (Copied from assets to sqlite database) .

i find top five libraries in this article i followed each library but what i find each database is working only with the database that created by itself not with existing database (Already copied from assets).

any help to use any of these library and refer it to my own database or any another library to help me .

Thanks

You can use [trie](https://www.toptal.com/java/the-trie-a-neglected-data-structure) — Ashraful Islam, Nov 02 '16 at 08:57
https://www.toptal.com/java/the-trie-a-neglected-data-structure — Ashraful Islam, Nov 02 '16 at 09:00

score 1 · Answer 1 · answered Nov 02 '16 at 09:05

You have to make sure existing database has the same format of data as new database you adding. Probably, the simpliest solution here - write some 'migration utility', which will extract existing data and save it to new database. To speed up queries store different aspects of data into separate tables (not everything in single table), or, if your data has many dependencies, try noSql database (Realm) which not using tables.

score 1 · Accepted Answer · edited May 23 '17 at 10:32

I suggest creating prepared statements and re-use them. There is an excellent answer on Stack Overflow how to do that. Consider reducing the columns returned by the query, if not all columns are needed. Further, consider creating indexes for relevant columns. test would be the table name and id a column name:

CREATE INDEX idx01 ON test(id);

As a last resort it might be worth trying to remove the LIKE completely and to the regex test while iterating over your cursor.

How to search in android sqlite database with speedy way?

2 Answers2