如何临时禁用Django索引(对于SQLite) [英] How to temporarily disable Django indexes (for SQLite)

问题描述

我正在尝试从大约500个较小的数据库(每个50-200MB)创建一个大型SQLite数据库，并将其放入Django，并希望加快此过程.我正在通过自定义命令执行此操作.

I'm trying to create a large SQLite database from around 500 smaller databases (each 50-200MB) to put into Django, and would like to speed up this process. I'm doing this via a custom command.

此答案对我的工作很有帮助，将处理小型数据库的速度降低到每分钟一分钟左右.但是，仍然有很长的时间.

This answer helped me a lot, in reducing the speed to around a minute each in processing a smaller database. However it's still quite a long time.

我在该答案中未完成的一件事是在Django中禁用数据库索引并重新创建它们.我认为这对我很重要，因为我的数据库很少有很多行的表.

The one thing I haven't done in that answer is to disable database indexing in Django and re-create them. I think this matters for me as my database has few tables with many rows.

在Django实时运行时，有没有办法做到这一点?如果不是在Django中，那么也许会有一些SQLite查询来删除所有索引，然后在我插入记录后重新创建它们?

Is there a way to do that in Django when it's running live? If not in Django then perhaps there's some SQLite query to remove all the indexes and re-create them after I insert my records?

推荐答案

我只是使用原始SQL删除索引并重新创建它们.这样将我的两个小型数据库中创建大型数据库的速度从1:46提高到了1:30，这非常重要.大小也从341.7MB减少到321.1MB.

I just used raw SQL to remove the indexes and re-create them. This improved the speed of creating a big database from 2 of my small databases from 1:46 to 1:30, so quite significant. It also reduced the size from 341.7MB to 321.1MB.

# Delete all indexes for faster database creation
with connection.cursor() as cursor:
    cursor.execute(f'SELECT name, sql FROM sqlite_master WHERE name LIKE "{app_label}_%" AND type == "index"')
    indexes = cursor.fetchall()
    names, create_sqls = zip(*indexes)
    for name in names:
        cursor.execute(f'DROP INDEX {name}')

创建数据库后，重新创建索引:

After I create the databases re-create the index:

# Re-create indexes
with connection.cursor() as cursor:
    for create_sql in create_sqls:
        cursor.execute(create_sql)

这篇关于如何临时禁用Django索引(对于SQLite)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！