如何优化带有日期范围where子句的删除？
我创建了一个android应用程序，有一个函数会定期删除旧记录，
delete from tablea where col1 = 'value1' and col2 = 'value2' and postdate < '2010-06-14'
当tablea中的总行数超过50,000时，它会出现性能问题。删除 500 条记录大约需要 45 秒。
我已经有这个 where 子句的索引：
CREATE INDEX indexa on tablea (col1, col2, postdate)
添加 PRAGMA synchronous=OFF 和 PRAGMA count_changes=OFF 没有帮助。
请指教
I created an android app and there is a function that will periodically delete old records
delete from tablea where col1 = 'value1' and col2 = 'value2' and postdate < '2010-06-14'
It has performance problem when total number of row in tablea has more then 50,000. It take around 45 seconds to delete 500 records.
I already have index for this where clause:
CREATE INDEX indexa on tablea (col1, col2, postdate)
adding PRAGMA synchronous=OFF and PRAGMA count_changes=OFF didn't help.
Please advise
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
检查您的后期字段的类型。
选择 typeof(postdate)。看起来 SQLite 将根据您的 where 子句将其视为TEXT。 SQLite 没有“日期”类型的概念，任何“日期”都会发生NUMERIC关联性。如果您没有插入NUMERIC，那么它可能会进行字符串比较，并且这会比您期望的整数比较慢。如果您要插入NUMERIC，那么您的 where 子句可能会导致它们首先转换为TEXT，然后再应用 where 子句条件。您可以查看有关数据类型的文档。
Check the type of your postdate field.
SELECT typeof(postdate). It looks like SQLite is going to treat it asTEXTbased on your where clause. SQLite has no concept of a 'date' type only aNUMERICaffinity is going to happen for any 'dates'. If you're not insertingNUMERICs then it's probably doing string comparisons and that is going to be slower than the integer comparisons that you are expecting. If you are insertingNUMERICs then your where clause may be causing them to be cast intoTEXTfirst and then the where clause criteria being applied.You can check out the documentation about datatypes.
在我看来，您的索引似乎没有所写的那么有用。我想你会想要：
正如所读，我对文档的解释说，要弄清楚示例中引用了哪一行，数据库需要有一个特定的日期来与给定的值对匹配（这样它就可以查找索引）。你没有具体的日期。相反，您正在寻找日期值的范围。
问题是日期值的可比性包含在您创建的组合索引中，因此您无法轻松获取该信息，这使得索引的用途受到限制。
当然，我可以充满它。我的主要经验不是使用 sqlite。不过，尝试上述方法应该不会有太大影响。
It looks to me as though your index isn't useful as written. I think you'd want:
As read, my interpretation of the documentation says that to figure out what row is being referred to in your example, the database needs to have a specific date to match with a given pair of values (so it can look up the index/indexes). You don't have a specific date. Instead you're looking for a range of values of date.
The problem is the comparability of the date values is subsumed in the combination index you created, so you can't get that information out very easily, which makes the index of limited use.
Of course I could be full of it. My primary experience isn't with sqlite. Still, it shouldn't hurt much to try the above.