加快sqlite3数据库搜索

I"m new to databases and just wrote my first code using sqlite3. It does the job, but is running extremely slowly and I'm hoping to get some advice regarding how to speed things up.

我是数据库新手，刚刚用sqlite3编写了我的第一个代码。它做的很好，但是运行非常缓慢，我希望得到一些关于如何加速的建议。

Right now my code looks something like this:

现在我的代码是这样的:

For Line in File:
   Line= Line.strip('\n').split('\n')
   Location = int(Line[1])
   MChr = Line[0]
cur = db.execute('''SELECT Start, End, Chr, Feature1, Feature2, Feature3, Feature4, FROM DataBase
                    WHERE Start <= ? AND End >= ? AND Chr == ?''', (Location, Location, MChr))
for (Start, Stop, Chr, Feature1, Feature2, Feature3, Feature4) in cur:
    if Feature1 == "A string":
        do something....
    if Feature2 == "A string":
        do something....

My database is a little over one million entries which is probably why my program is running slow but I was wondering if there is a way to make the search more efficient to circumvent having to run through all million for every line. (Perhaps first pull out all matching Chrs?)

我的数据库有超过100万的条目，这可能就是为什么我的程序运行缓慢的原因，但是我想知道是否有一种方法可以使搜索更加高效，从而避免每一行都要运行100万。(或许首先要取出所有匹配的Chrs?)

2 个解决方案

#1

You should index your db:

你应该索引你的数据库:

http://www.sqlite.org/lang_createindex.html

This should speed things up.

这应该会加快速度。

#2

Create indexes on the columns in question. If your table name is DataBase, then try something like:

在相关列上创建索引。如果您的表名是数据库，那么尝试以下操作:

db.execute('''CREATE UNIQUE INDEX Start_index ON `DataBase` (`Start`(64))''')
db.execute('''CREATE UNIQUE INDEX End_index ON `DataBase` (`End`(64))''')
db.execute('''CREATE UNIQUE INDEX Chr_index ON `DataBase` (`Chr`(64))''')

#1