SQLite3 how to use indexes?

Question

Im working with SQLite3 indexes.

Here is the COMAPNY table:

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 

Allows you to create a pay_index index,

 CREATE INDEX IF NOT EXISTS salary_index on COMPANY (SALARY); 

What does he do and how to use it?

This time I do this index after deleting the old one:

 CREATE INDEX IF NOT EXISTS salary_index on COMPANY (SALARY) WHERE SALARY > 50000; 

After adding the index, I did:

 SELECT * FROM COMPANY; 

expecting Id to see only those whose salaries are above 50,000, but I have seen people below that.

And I also tried to do this:

 SELECT * FROM COMPANY INDEXED BY salary_index; 

Then I get Error: there is no solution for requests Apparently, I have to do: CHOOSE * FROM A COMPANY SIGNED BY HEALTH VALUE WHERE SALARY> 50000; If the condition should be the same as in the index.

So ... how to use indexes?

Answer 1

Indexes never change the meaning of your queries. What they can do is speed up some of your queries; when possible, they are used automatically.

The index is useful for

• search records with comparisons in an indexed column:

   SELECT * FROM Company WHERE Salary = 20000.0; SELECT * FROM Company WHERE Salary BETWEEN 40000 AND 80000; 

  which also includes joins in the indexed column; and

• sorting records:

   SELECT * FROM Company ORDER BY Salary 

  which also includes GROUP BY and DISTINCT.

See the documentation for more details:
Request Planning
SQLite Query Scheduler

Answer 2

Here is the conversation that I had with one of my code masters (thanks S.P.):

An index is usually a tool for performance. If you do not have an index for the field, the queries in this field should perform 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 more rows, then a full sequential scan is too slow.

So, if you want to get rows for which the salary is <50,000, just create an index in the table and then release

 SELECT * FROM COMPANY WHERE SALARY < 50000 

It will automatically use the correct indexes by length, since the SALARY field is indexed

So, if we have two type indexes

 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; 

tt automatically uses the above indexes for the query.

In most DBMSs, you can use more than one index in a single query, and yes, they are automatically used if they are applied. But there may be limitations on this, and they are specific to RBDMS. But it’s better to create an index containing several fields.

In the optimal situation, you should have all the fields necessary for the request in one index. Therefore, if you want employees to earn more than $ 50,000 and younger than 40 years, you would specify the following index:

 CREATE INDEX company_salary_age ON company (salary, age); 

The order of the fields matters. This index can be used in a query with a WHERE clause about salary, salary and age , but not age without salary . That is, any number of index fields can be used as long as they are adjacent in front of the index. That is, in the request you can skip fields from the end, but not at the beginning or in the middle .