Hello again !
I was trying to use the alias in the where clause hopping sqlite would be
smart enough to recognize it and use the index but that doesn't happen.
So I tried again repeating the expression instead of using the alias and now
sqlite recognize the index and run very fast.
Would be nice to sqlite be able to recognize aliases and also do not call
column expressions multiple times.
Cheers !
________Output
Time to insert??? 5000??? 0.035002??? records by second = ???
142849
count=??? 4999
json=??? the_value_1
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select raw??? 5000??? 0.002689??? records by second = ???
1.85943e+06
Time to select raw one by one??? 5000??? 7.60242??? records by
second = ??? 657.685
Time to select json_extrat no index??? 5000??? 7.59418??? records by
second = ??? 658.399
Time to create index??? 5000??? 0.006153??? records by second =
??? 812612
??? 0??? 0??? 0??? SEARCH TABLE json_tbl USING INDEX json_tbl_idx
(<expr>=?)
Time to select json_extrat indexed??? 5000??? 0.034494??? records by
second = ??? 144953
________
________Program
local max_count = 5000;
local sql = "CREATE TABLE IF NOT EXISTS json_tbl(id integer primary key, json
text collate nocase);";
local db = SQLite3(":memory:");
db.exec_dml(sql);
local stmt = db.prepare("insert into json_tbl(json) values(?);");
local start = os.clock();
db.exec_dml("begin;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
i));
??? stmt.step();
??? stmt.reset();
}
stmt.finalize();
db.exec_dml("commit;");
local time_spent = os.clock() -start;
print("Time to insert", max_count, time_spent, "records by second = ",
max_count/time_spent);
print("count=", db.exec_get_one("select count(*) from json_tbl"));
print("json=", db.exec_get_one("select json_extract(json, '$.value') AS val
from json_tbl? where val = 'the_value_1';"));
sql = "select? json_extract(json, '$.value') AS val? from json_tbl where
json_extract(json, '$.value') = ?;";
local showPlan = function()
{
??? stmt = db.prepare("explain query plan " + sql);
??? while(stmt.next_row())
??? {
??? ??? local line = "";
??? ??? for(local i=0, len = stmt.col_count(); i < len; ++i)
??? ??? {
??? ??? ??? line += "\t" + stmt.col(i);
??? ??? }
??? ??? print(line);
??? }
??? stmt.finalize();
}
showPlan();
start = os.clock();
stmt = db.prepare("select * from json_tbl");
while(stmt.next_row())
{
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select raw", max_count, time_spent, "records by second = ",
max_count/time_spent);
start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select raw one by one", max_count, time_spent, "records by
second = ", max_count/time_spent);
start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select json_extrat no index", max_count, time_spent, "records
by second = ", max_count/time_spent);
start = os.clock();
db.exec_dml("create index json_tbl_idx on json_tbl(json_extract(json,
'$.value'));");
time_spent = os.clock() -start;
print("Time to create index", max_count, time_spent, "records by second = ",
max_count/time_spent);
showPlan();
start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select json_extrat indexed", max_count, time_spent, "records
by second = ", max_count/time_spent);
/*
local function unql_exec(db, sql)
{
??? local the_stmt = db.prepare(sql);
??? local rc = the_stmt.step();
??? the_stmt.finalize();
??? return rc;
}
start = os.clock();
local db_unql = xjd1(db);
unql_exec(db_unql, "CREATE COLLECTION unql_json;");
stmt = db_unql.prepare("INSERT INTO unql_json VALUE ?;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
i));
??? stmt.step();
??? stmt.reset();
}
stmt.finalize();
stmt = db_unql.prepare("SELECT FROM unql_json WHERE unql_json.value = ?;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select unql", max_count, time_spent, "records by second = ",
max_count/time_spent);
*/
//db.backup("json.db");
db.close();
________
