CODEWITHSUNDEEP
androidsqlite
arams
arams

Reputation: 2301

Best way to search sqlite database

In my application ,am work with a large database.Nearly 75000 records present in a table(totally 6 tables are there).i want to get a data from three different table at a time.i completed that.but the search process was slow.how can i optimise the searching process?

Upvotes: 0

Views: 6263

Answers (2)

Mike Bouck
Mike Bouck

Reputation: 576

You might want to consider using the full-text search engine and issuing SELECT...MATCH queries instead. Note that you need to enable the FTS engine (it's disabled by default) and create virtual tables instead of regular tables. You can read more about it here.

Upvotes: 1

Jeremy Logan
Jeremy Logan

Reputation: 47514

Without being able to see the table structure (or query) the first thing I'd suggest is adding some indexes to the tables.

Lets say you have a few tables like:

Author
    id
    last_name
    first_name

Subject
    id
    name

Book
    id
    title
    author_id
    subject_id

and you're wanting to get all the information about each of the books that an author with last_name="Smith" and first_name="John" wrote. Your query might look something like this:

SELECT * FROM Book b 
    LEFT JOIN Subject s 
        ON s.id=b.subject_id
    LEFT JOIN Author a 
        ON a.id=b.author_id
    WHERE a.last_name='Smith' 
        AND a.first_name='John';

There you'd want the last_name column in the Author table to have an index (and maybe first_name too).

Upvotes: 0

Related Questions