Hi,
I’m trying to define an Index to an SQLite table to improve the response time when retriving data.
I have a a Score value on the table which should be indexed. Unfortunately the score may be repeated so I can’t define it as the primary key.
So I define an index, the problem is that when retriving the data, it fires an error.
I attach my code where I define the Index and how I do the SELECT query. Hope someone will find a mistake.
--Setup the table if it doesn't exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS resultats (
id INTEGER PRIMARY KEY autoincrement,
score INTEGER,
level INTEGER
);]]
--print(tablesetup)
db:exec( tablesetup )
local indexsetup = [[CREATE INDEX IF NOT EXIST index1 ON resultats (score DESC);]]
print(indexsetup)
db:exec( indexsetup )
for i=1, 1000 do
local insertsetup = [[INSERT INTO resultats VALUES (
NULL, ]]..
math.random(0,10000)..[[,]]..
math.random(1,15)..[[
)]]
--print(insertsetup)
db:exec( insertsetup )
end
--print all the table contents
for row in db:nrows("SELECT \* FROM resultats INDEXED BY index1") do
print()
for key,value in pairs(row) do
print(key..": "..value)
end
print()
end Thanks! [import]uid: 76413 topic_id: 27784 reply_id: 327784[/import]