CODEWITHSUNDEEP
sqliteselectperformance
ahmd0
ahmd0

Reputation: 17293

Speed up SQL select in SQLite

I'm making a large database that, for the sake of this question, let's say, contains 3 tables:

A. Table "Employees" with fields:

id = INTEGER PRIMARY INDEX AUTOINCREMENT

Others don't matter

B. Table "Job_Sites" with fields:

id = INTEGER PRIMARY INDEX AUTOINCREMENT

Others don't matter

C. Table "Workdays" with fields:

id = INTEGER PRIMARY INDEX AUTOINCREMENT

emp_id = is a foreign key to Employees(id)

job_id = is a foreign key to Job_Sites(id)

datew = INTEGER that stands for the actual workday, represented by a Unix date in seconds since midnight of Jan 1, 1970

The most common operation in this database is to display workdays for a specific employee. I perform the following select statement:

SELECT * FROM Workdays WHERE emp_id='Actual Employee ID' AND job_id='Actual Job Site ID' AND datew>=D1 AND datew<D2

I need to point out that D1 and D2 are calculated for the beginning of the month in search and for the next month, respectively.

I actually have two questions:

  1. Should I set any fields as indexes besides primary indexes? (Sorry, I seem to misunderstand the whole indexing concept)

  2. Is there any way to re-write the Select statement to maybe speed it up. For instance, most of the checks in it would be to see that the actual employee ID and job site ID match. Maybe there's a way to split it up?

PS. Forgot to say, I use SQLite in a Windows C++ application.

Upvotes: 1

Views: 2265

Answers (1)

Paul Lefebvre
Paul Lefebvre

Reputation: 6406

If you use the above query often, then you may get better performance by creating a multicolumn index containing the columns in the query:

CREATE INDEX WorkdaysLookupIndex ON Workdays (emp_id, job_id, datew);

Sometimes you just have to create the index and try your queries to see what is faster.

Upvotes: 2

Related Questions