CODEWITHSUNDEEP
databaseperformancesqlitelua
akobre01
akobre01

Reputation: 827

lua and lsqlite3: speeding up select statement

I'm using the lsqlite3 lua wrapper and I'm making queries into a database. My DB has ~5million rows and the code I'm using to retrieve rows is akin to:

db = lsqlite3.open('mydb')
local temp = {}
local sql = "SELECT A,B FROM tab where FOO=BAR ORDER BY A DESC LIMIT N"
for row in db:nrows(sql) do temp[row['key']] = row['col1'] end

As you can see I'm trying to get the top N rows sorted in descending order by FOO (I want to get the top rows and then apply the LIMIT not the other way around). I indexed the column A but it doesn't seem to make much of a difference. How can I make this faster?

Upvotes: 1

Views: 528

Answers (1)

user610650
user610650

Reputation:

You need to index the column on which you filter (i.e. with the WHERE clause). THe reason is that ORDER BY comes into play after filtering, not the other way around.

So you probably should create an index on FOO.

Can you post your table schema?

UPDATE

Also you can increase the sqlite cache, e.g.:

PRAGMA cache_size=100000

You can adjust this depending on the memory available and the size of your database.

UPDATE 2

I you want to have a better understanding of how your query is handled by sqlite, you can ask it to provide you with the query plan:

http://www.sqlite.org/eqp.html

UPDATE 3

I did not understand your context properly with my initial answer. If you are to ORDER BY on some large data set, you probably want to use that index, not the previous one, so you can tell sqlite to not use the index on FOO this way:

SELECT a, b FROM foo WHERE +a > 30 ORDER BY b

Upvotes: 3

Related Questions