搜索sqlite数据库的最佳方法

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?

在我的应用程序中,我使用的是一个大型数据库。表中有近75000条记录(共有6个表)。我想一次从三个不同的表中获取数据。我完成了。但搜索过程很慢。我可以优化搜索过程吗?

2 个解决方案

#1

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.

您可能需要考虑使用全文搜索引擎并发出SELECT ... MATCH查询。请注意,您需要启用FTS引擎(默认情况下已禁用)并创建虚拟表而不是常规表。你可以在这里读更多关于它的内容。

#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:

并且您希望获得有关last_name =“Smith”和first_name =“John”的作者所写的每本书的所有信息。您的查询可能如下所示:

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

在那里,您希望Author表中的last_name列具有索引(也可能是first_name)。

#1

#2