CODEWITHSUNDEEP
iphoneiossqliteios5
anshul
anshul

Reputation: 846

SQLite Database taking too much time to retrieve (or fetch some data)

I have a sqlite cipher database with around 80000 records (containing 7 columns). Its currently taking too much time in searching the records. I need an optimized way of searching for 1 or more record based on some criteria (need something like indexing) so that I can reduce the searching time. Does anybody has any idea about how to proceed to achieve this? Need help urgently. :(

EDITED: I am using sqlite cipher API because I need to store the encrypted data in sqlite database. I have set the pragma key and the encryption being used is AES - 256. I have also created the index on each table in my database. But my searching is not fast. Its taking too much time. Need to solve this ASAP.

2nd TIME EDITED: Hey guys, I am badly stuck this time. I am making a search as soon as user clicks on a text field. To summarize the problem, let me tell you the flow.

I have 2 text fields and a table containing ~80k records and 7 columns. When I type anything in the field1, a search is made onto the table with the content of the field1. So lets say I type 'a' in my field1, then a select query returns all the records containing 'a' letter in column1. So, the table is being searched as soon as I type the letters. Uptill now there is no problem. Its returning the records without any time delay. The query is:

SELECT DISTINCT <COL1> COLLATE NOCASE as <COL1> from <TABLE_NAME> where <COL1> like '%ab%'   LIMIT 0,501

Now, if I click on field 2, then I see the cursor on field2 after 1 secs as this query gets execute:

SELECT DISTINCT <COL2> COLLATE NOCASE as <COL2> from <TABLE_NAME> where <COL1> like '%ab%'   LIMIT 0,501

Now, the db fetching process is taking time if I type anything on field2. Here is the final query:

SELECT DISTINCT <COL2> COLLATE NOCASE as <COL2> from <TABLE_NAME> where <COL1> like '%ab%' AND <COL2> like '%cd%'  LIMIT 0,501

When I tried to analyse the problem, I came to know that the problem is due to DISTINCT Keyword. I have created indices on the columns as follows:

CREATE INDEX <name1> on <TABLE_NAME>(<COL1>)
CREATE INDEX <name2> on <TABLE_NAME>(<COL1> COLLATE CASE)
CREATE INDEX <name3> on <TABLE_NAME>(<COL2>)
CREATE INDEX <name4> on <TABLE_NAME>(<COL2> COLLATE CASE)

Anyone has any idea how to make this retrieval fast.

Upvotes: 0

Views: 1295

Answers (2)

user187676
user187676

Reputation:

I assume you use the SQLite amalgamation source. If you need full text search too, you can add this define on top of sqlite3.c (or what values do you need searchable?).

#define SQLITE_ENABLE_FTS3

Then create an index using the docs VeryVito mentioned.

Upvotes: 0

Mike Fahy
Mike Fahy

Reputation: 5707

Something like indexing? SQLite is fully capable of using indexes, as explained here in the docs. The structure and purpose of your tables will determine how best to implement them, though.

Upvotes: 2

Related Questions