Best way to search sqlite database

Question

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?

Answer 1

If you post your query, someone would probably try to optimise it.

For a start try creating index on some columns.

Answer 2

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).

Answer 3

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.