CODEWITHSUNDEEP
sqlsqliteindexingquery-optimization
tryingToLearn
tryingToLearn

Reputation: 11659

SQLITE: How to make indexing work for you?

I have a sqlite db of employees with about a million entries.

company:

emp_id(primary) | first_name | last_name | company_name | job_title

The db contains only 10 distinct company names (i.e. let's say each company has about 100k employees) I created an index on company name:

CREATE INDEX cmp_name ON company(company_name)

But I have not gained any speed while performing query:

WITH INDEX:

select * from company INDEXED BY cmp_name where company_name = 'XYZ corp';
Time: 88.45 sec

WITHOUT INDEX:

select * from company where company_name = 'XYZ corp';
Time: 89.12 sec

What am I doing wrong?

Upvotes: 1

Views: 34

Answers (1)

CL.
CL.

Reputation: 180060

A database is organized into pages. If more than ten rows fit into a page, then on average, reading all the "XYZ Corp" rows still requires reading most pages. Furthermore, with the index entries not having the same order as the table rows, the table's page are no longer read in order.

The only way to speed up this query would be to use a covering index. First, reduce the number of columns read to the absolute minimum that you actually need, then add all those columns to the company name index (the INTEGER PRIMARY KEY column is implicitly part of every index):

CREATE INDEX cmp_name_and_other_stuff ON company(company_name, last_name);

SELECT emp_id, last_name FROM company WHERE company_name = 'XYZ Corp';

Doing this for every query will waste lots of storage space.

Upvotes: 3

Related Questions