SQLite3 how do I use indices?

Question

I’m working on SQLite3 indices.

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;

Results:

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

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;

After I added the index, I did:

SELECT * FROM COMPANY;

expecting that I’d see only the ones with salary higher than 50000, but I saw people lower than that.

And I also tried to do this:

SELECT * FROM COMPANY INDEXED BY salary_index;

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?

Answer 1

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.

An index is useful for

• 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

• sorting records:

  SELECT * FROM Company ORDER BY Salary
  

  which also includes GROUP BY and DISTINCT.

See the documentation for details:
Query Planning
The SQLite Query Planner

Answer 2

Here is the conversation I had with one of my code master(Thanks S.P.):

An index is usually a tool for performance. If you do not have an index for a field, queries on that field will need to do a full sequential scan of the table. This is not a problem if the table is small but if you have tens of thousands, or above rows, then a full sequential scan is simply too slow.

So if you want to get the rows for which salary < 50000, just create an index on the table, and then issue

SELECT * FROM COMPANY WHERE SALARY < 50000

It will automatically use the correct indexas long as the SALARY field is indexed

So if we have two indexes like

CREATE INDEX salary_index WHERE salary < 50000;
CREATE INDEX age_index WHERE age < 40;

and then we run a query like

SELECT * FROM COMPANY WHERE salary < 50000 AND age < 40;

It automatically uses the above 2 indices for the query.

In most RDBMSs, it is possible to use more than one index in a single query and yes, they're used automatically if they apply. But there might be restrictions on this and they're RBDMS specific. But a better idea is to create an index that contains multiple fields.

In an optimal situation, you would have all the fields needed by the query in a single index So if you want employees that earn more than 50 000 $ and are younger than 40 years you would define an index like this:

CREATE INDEX company_salary_age ON company (salary, age);

The order of the fields matters. This index can be used in a query that has a WHERE clause on salary, or salary and age, but not age without salary. That is, any number of fields of the index can be used as long as they are contiguous in the front of the index That is, in the query you can omit fields from the end, but not in the beginning or middle.