CODEWITHSUNDEEP
androidandroid-sqliteandroid-database
Mina Farid
Mina Farid

Reputation: 5887

How to search in android sqlite database with speedy way?

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

Upvotes: 0

Views: 79

Answers (2)

user5956451
user5956451

Reputation:

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.

Upvotes: 1

Alex Shutov
Alex Shutov

Reputation: 3282

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.

Upvotes: 1

Related Questions