Is there any way to optimize the time for reading at the expense of the times for the update?
Let me explain. I have a database with a table of 4,000,000 words. An index is word length. I am looking for words in this table based on word length. For example, I want to grab all the words that have a length of 5 letters and with the pattern "TY ?? A". I get 12.275 words when reading the database. 7 words of its match my word patterns. This takes 3.214 seconds which the actual reading of the database takes 2.743 seconds. This means that the reading of the database is the bottleneck. There are thousands of such a search per month.
The updating of the database occurs once per month and the time is irrelevant.
So, back to my question: Can I do something so the reading is faster at the expense of time for the update?
I have built new and better index an now is performace very good.
Seeking for TY??A takes 0.188 seconds instead of 3.214.
May we see it?
Did you run your routine through the query planner in Sqlite3.exe?
Some searches may seem fast with cached results and other factors, but are not using the index.
To be fast the query plan should always say SEARCH and not SCAN (which scans entire table.)
I could not get LIKE to use the index without the following:
TEXT COLLATE NOCASE on the create statement and of course creating an index.
For anyone not familiar with the command line tool, it is excellent for testing.
I have include a little batch file named EASY.BAT and the command sent to it in EASY.TXT at the bottom of this thread.
#DIM ALL 'like.bas testing using explain query plan in SQlite3.exe command line tool
#INCLUDE "sqlitening.inc"
%DropTable = 1 'set to start with a new table
%InsertRecords = 999 'number of records that won't match + 1 valid record
%Loops = 4 'insert records this many times
%JustSearch = 0 'don't insert any more records
FUNCTION PBMAIN () AS LONG
LOCAL InsideLoop,OutsideLoop,InsertRecords,Loops AS LONG
LOCAL sArray() AS STRING
InsertRecords = %InsertRecords '%InsertRecords can't be 0 in FOR/NEXT
Loops = %Loops '%Loops can't be 0 in FOR/NEXT
slopen "sample.db3","C"
IF %DropTable THEN slexe "drop table if exists WordTable"
'slexe "create table if not exists WordTable(Number integer primary key, Word not null collate nocase)" ;'scan bad needs text
slexe "create table if not exists WordTable(Number integer primary key, Word Text not null collate nocase)" 'search good
'slexe "create table if not exists WordTable(Number integer primary key, Word Text collate nocase)" 'search good
'slexe "create table if not exists WordTable(Number integer primary key, Word Text)" 'bad, scans needs collate nocase
slexe "create index if not exists Word_idx on WordTable(Word)" 'required else SCAN entire table
IF %JustSearch = 0 THEN
slexe "Begin exclusive"
IF %InsertRecords > 0 AND %Loops > 0 THEN
slexe "drop index if exists Word_Idx" 'drop index for mass inserts
FOR OutSideLoop = 1 TO Loops
FOR InsideLoop = 1 TO InsertRecords
slexe "insert into WordTable values(null,'dummy')"
NEXT InsideLooop
slexe "insert into WordTable values(null,'TYroa')"
NEXT OutSideLoop
slexe "create index if not exists Word_idx on WordTable(Word)" 'put index back z
slexe "end"
END IF
END IF
slSelAry "select Number,Word from WordTable where Word Like('TY__A')",sArray(),"Q9"
? JOIN$(sArray(),$CR),,FORMAT$(UBOUND(sArray))
END FUNCTION
'---- sqlite query planner ----
'https://www.sqlite.org/optoverview.html
The command-line shell program (version 3.8.10.1).
easy.bat
@cls
@echo off
sqlite3.exe sample.db3 < easy.txt
@echo on
rem easy.txt
.separator | \r\n
.headers on
.mode column
.width 8 8 8 4 6 6 7 13 -3
.schema WordTable
.echo on
.explain on
explain query plan
select number,word
from WordTable
Where Word Like 'TY__A';
select number,word from wordtable where word like 'TY__A';
-- ORDKKORS.D.DB3 Distribuerad databas
-- ===================================
Drop Table if Exists Wordlist;
Create Table Wordlist(
Length Integer not null ,
Word Text not null,
Group Text not null ,
Unique (Word, Length));
Drop Index if exists Length_idx;
Create Index Length_idx on Wordlist (Length);
Drop Index if exists Word_idx;
Create Index Word_idx on Wordlist (Word);
Drop Index if exists Group_idx;
Create Index Group_idx on Wordlist (Group);
-------------------
I can not use the LIKE because the search pattern is a bit more complex, some exempels
A??2?2A Means that letters 4 and 6 must be the sama letter
123456789 Means that all letters must be different
QW,.? Means that the third letter will be a vowel and the fourth a vowel
E?
"Did you run your routine through the query planner in Sqlite3.exe?"
I use a PowerBasic-program.
All testing is done in the program that will be distributed to the users.
The last edition is available here www.ordkors.se
It seems like you have it under control.
For others:
The query planner is on the SQLite site and used to be sure sql statements are optimized.
If you haven't used the command line shell, you might give it a try to examine the query plan.
Fim,
"Did you run your routine through the query planner in Sqlite3.exe?"
I meant you can test indexes to be sure they are totally used with explain statement:
http://www.sqlitening.com/support/index.php?topic=9369.0