sqlite first executed query slow after opening connection

Question

I create an sqlite3 database (using SQLite Expert Professional) with 1 table and more than 500,000 records; If I command a simple query like:

select * from tableOne where entry like 'book one'

if it's my first command to be executed after connecting to database, it takes a considerably long time to be executed and retrieve the result(~15seconds) but just after first command, everything comes back to normal and now every command executes with a very acceptable speed;

even if I close my application(I use pure LUA with sqlite modules)(and within it's logic, reasonably close all connections) as long as Windows(8 x64) is running an not restarted, every command even the first one executes very well but after restarting windows, again, like always first command is slow to be executed;

what is the reason? how can I prevent this?

Answer 1

Most likely after the first time you run this, you've loaded cache up with all your data, so subsequent queries are fast. Do you have an index on entry? An index will allow efficient querying using entry as a filter. You may want to create one:

CREATE INDEX i_tableone_entry ON tableOne( entry );