加快 SQLite 中的 SQL 选择速度
我正在创建一个大型数据库，为了解决这个问题，假设包含 3 个表：
A.表“员工”，包含字段：
id = INTEGER PRIMARY INDEX AUTOINCRMENT
其他不重要
B.表“Job_Sites”，包含字段：
id = INTEGER PRIMARY INDEX AUTOINCRMENT
其他不重要
C.表“Workdays”，包含以下字段：
id = INTEGER PRIMARY INDEX AUTOINCRMENT
emp_id = 是Employees(id) 的外键
job_id = 是Job_Sites(id) 的外键
datew = INTEGER 代表实际工作日，表示按自 1970 年 1 月 1 日午夜以来的 Unix 日期（以秒为单位）
此数据库中最常见的操作是显示特定员工的工作日。我执行以下 select 语句：
SELECT * FROM Workdays WHERE emp_id='Actual Employee ID' AND job_id='Actual Job Site ID' AND datew>=D1 AND datew
我需要指出，D1 和 D2 是针对分别是搜索的月初和下个月。
我其实有两个问题：
除了主索引之外，我还应该设置任何字段作为索引吗？ （抱歉，我似乎误解了整个索引概念）
有什么方法可以重写 Select 语句以加快速度。例如，其中的大多数检查都是为了查看实际员工 ID 和工作地点 ID 是否匹配。也许有办法将其拆分？
附言。忘了说，我在 Windows C++ 应用程序中使用 SQLite。
I'm making a large database that, for the sake of this question, let's say, contains 3 tables:
A. Table "Employees" with fields:
id = INTEGER PRIMARY INDEX AUTOINCREMENT
Others don't matter
B. Table "Job_Sites" with fields:
id = INTEGER PRIMARY INDEX AUTOINCREMENT
Others don't matter
C. Table "Workdays" with fields:
id = INTEGER PRIMARY INDEX AUTOINCREMENT
emp_id = is a foreign key to Employees(id)
job_id = is a foreign key to Job_Sites(id)
datew = INTEGER that stands for the actual workday, represented by a Unix date in seconds since midnight of Jan 1, 1970
The most common operation in this database is to display workdays for a specific employee. I perform the following select statement:
SELECT * FROM Workdays WHERE emp_id='Actual Employee ID' AND job_id='Actual Job Site ID' AND datew>=D1 AND datew<D2
I need to point out that D1 and D2 are calculated for the beginning of the month in search and for the next month, respectively.
I actually have two questions:
Should I set any fields as indexes besides primary indexes? (Sorry, I seem to misunderstand the whole indexing concept)
Is there any way to re-write the Select statement to maybe speed it up. For instance, most of the checks in it would be to see that the actual employee ID and job site ID match. Maybe there's a way to split it up?
PS. Forgot to say, I use SQLite in a Windows C++ application.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您经常使用上述查询，那么您可以通过创建包含查询中的列的多列索引来获得更好的性能：
有时您只需创建索引并尝试查询以查看哪个更快。
If you use the above query often, then you may get better performance by creating a multicolumn index containing the columns in the query:
Sometimes you just have to create the index and try your queries to see what is faster.