mercredi 6 janvier 2016

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 1 lac 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?

Aucun commentaire:

Enregistrer un commentaire