#!/usr/bin/env tarantool
box.cfg { pid_file = "tarantool_test/pid.pid", log = "tarantool_test/tarantool.log", wal_dir = "tarantool_test", memtx_dir = "tarantool_test", vinyl_dir = "tarantool_test"}
--box.execute("pragma sql_default_engine = 'vinyl'");box.execute("CREATE TABLE IF NOT EXISTS tester (s1 INT PRIMARY KEY, s2 VARCHAR(10))");
local function string_function() local random_number local random_string random_string = "" for x = 1,10,1 do random_number = math.random(65, 90) random_string = random_string .. string.char(random_number) end return random_stringend;
local function main_function() local string_value, t, sql_statement for i = 1,1000000,1 do string_value = string_function() sql_statement = "INSERT INTO tester VALUES (" .. i .. ",'" .. string_value .. "')" box.execute(sql_statement) endend;
local start_time = os.clock();main_function();local end_time = os.clock();print('insert done in ' .. end_time - start_time .. ' seconds');
start_time = os.clock()box.execute("SELECT * FROM tester WHERE s1=736")end_time = os.clock()print('SELECT index done in ' .. end_time - start_time .. ' seconds')
start_time = os.clock()box.execute("SELECT * FROM tester WHERE s2 LIKE 'QFML%'")end_time = os.clock()print('SELECT LIKE done in ' .. end_time - start_time .. ' seconds')
tarantool test2.lua
insert done in 37.64 seconds
SELECT index done in 0 seconds
SELECT LIKE done in 0.61 seconds
tarantool test2.lua
insert done in 41.63 seconds
SELECT index done in 0 seconds
SELECT LIKE done in 3.69 seconds
SQLite version 3.7.17
sqlite> .timer on
sqlite> SELECT * FROM tester WHERE s1=736;
CPU Time: user 0.000118 sys 0.000002
sqlite> SELECT * FROM tester WHERE s2 LIKE 'QFML%';
CPU Time: user 0.883653 sys 0.016357
Tarantool 2.2.2-4-g4f8ac59
SELECT * FROM tester WHERE s2 LIKE 'QFML%';
UPDATE tester SET s2='13123' WHERE s2='DSJNBGTYFE';
box.execute("CREATE INDEX s2_idx ON tester (s2)");
box.execute("SELECT * FROM tester WHERE s2 LIKE 'QFML%'");
tarantool> \set language sql
---
- true
...
tarantool> EXPLAIN QUERY PLAN SELECT * FROM tester WHERE s2 LIKE 'QFML%'
---
- metadata:
- name: selectid
type: integer
- name: order
type: integer
- name: from
type: integer
- name: detail
type: text
rows:
- [0, 0, 0, 'SEARCH TABLE TESTER USING COVERING INDEX S2_IDX (S2>? AND S2<?) (~16384
rows)']
...
tarantool> EXPLAIN QUERY PLAN SELECT * FROM tester WHERE s2 LIKE '%QFML%'
---
- metadata:
- name: selectid
type: integer
- name: order
type: integer
- name: from
type: integer
- name: detail
type: text
rows:
- [0, 0, 0, 'SCAN TABLE TESTER (~983040 rows)']
...
select count(s1) from tester;
box.space.tester:count(1, {iterator='GE'})
box.space.tester:len()