SQLite3如何使用索引? [英] SQLite3 how do I use indices?
问题描述
我正在研究SQLite3索引.
I’m working on SQLite3 indices.
这是一张桌子COMAPNY:
Here’s a table COMAPNY:
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
================================================ ========
=======================================================
SELECT * FROM COMPANY;
结果:
1|Paul|32|California|20000.0
2|Allen|25|Texas|15000.0
3|Teddy|23|Norway|20000.0
4|Mark|25|Rich-Mond |65000.0
5|David|27|Texas|85000.0
6|Kim|22|South-Hall|45000.0
7|James|24|Houston|10000.0
让我们创建一个索引salary_index，
Let’s create an index salary_index,
CREATE INDEX IF NOT EXISTS salary_index on COMPANY (SALARY);
它有什么作用，怎么使用?
What does it do and how do I use it?
这一次，我在删除较旧的索引后创建了这样的索引:
This time I make an index like this after dropping the older one:
CREATE INDEX IF NOT EXISTS salary_index on COMPANY (SALARY)
WHERE SALARY > 50000;
添加索引后，我做了:
SELECT * FROM COMPANY;
期望我只会看到薪水高于50000的人，但是我看到薪水低于此值的人.
expecting that I’d see only the ones with salary higher than 50000, but I saw people lower than that.
我也尝试这样做:
SELECT * FROM COMPANY INDEXED BY salary_index;
然后我得到错误:没有查询解决方案 显然我必须这样做: 从薪水索引中选择*从公司索引中薪水> 50000; 条件必须与索引中的条件相同.
Then I get Error: no query solution Apparently I have to do: SELECT * FROM COMPANY INDEXED BY salary_index WHERE SALARY > 50000; Where the condition must be the same as in the index.
那么...我该如何使用索引?
So… how do I use indices?
推荐答案
索引永远不会改变查询的含义. 他们可以做的是加快您的某些查询；在可能的情况下，它们会自动使用.
Indexes never change the meaning of your queries. What they can do is to speed up some of your queries; when that is possible, they are used automatically.
索引对于
-
在索引列上查找具有比较的记录:
looking up records with comparisons on the indexed column:
SELECT * FROM Company WHERE Salary = 20000.0;
SELECT * FROM Company WHERE Salary BETWEEN 40000 AND 80000;
还包括索引列上的联接；和
which also includes joins on the indexed column; and
排序记录:
SELECT * FROM Company ORDER BY Salary
其中还包括GROUP BY和DISTINCT.
which also includes GROUP BY and DISTINCT.
有关详细信息，请参阅文档:
查询计划
SQLite查询计划器
See the documentation for details:
Query Planning
The SQLite Query Planner
这篇关于SQLite3如何使用索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
