Introduction
I have some strings in my database. I would like to query them. Also, I want the results quick. Simple, use SQL LIKE and put an index on it.
Oh wait, that's weird, it didn't work like how I want... this blog covers the common traps with searching text strings in SQLite.
Scenario
Let's look at a sample database table. This one is representing property parcels and their very famous owners.
<SPAN class="keyword token">CREATE</SPAN> <SPAN class="keyword token">TABLE</SPAN> parcel
<SPAN class="punctuation token">(</SPAN>
owner <SPAN class="keyword token">TEXT</SPAN>
<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">INSERT</SPAN> <SPAN class="keyword token">INTO</SPAN> parcel <SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">values</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Bill Gates'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">INSERT</SPAN> <SPAN class="keyword token">INTO</SPAN> parcel <SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">values</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Steve Jobs'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">INSERT</SPAN> <SPAN class="keyword token">INTO</SPAN> parcel <SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">values</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Jack Dangermond'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">INSERT</SPAN> <SPAN class="keyword token">INTO</SPAN> parcel <SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">values</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Steve Wozniak'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">INSERT</SPAN> <SPAN class="keyword token">INTO</SPAN> parcel <SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">values</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Tim Cook'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">INSERT</SPAN> <SPAN class="keyword token">INTO</SPAN> parcel <SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">values</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Mark Zuckerberg'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
We will be running queries on the above data, similar to:
<SPAN class="keyword token">SELECT</SPAN> <SPAN class="operator token">*</SPAN> <SPAN class="keyword token">FROM</SPAN> parcel<SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN></SPAN>
and, at the same time, we will be studying the query's execution plan with something similar to:
<SPAN class="keyword token">EXPLAIN</SPAN> QUERY <SPAN class="keyword token">PLAN</SPAN> <SPAN class="keyword token">SELECT</SPAN> <SPAN class="operator token">*</SPAN> <SPAN class="keyword token">FROM</SPAN> parcel<SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN></SPAN>
Using and optimizing LIKE
Once you dabble in a bit of SQL you'll quickly realize that you need to use LIKE for your string searches. For example, if we want to find all owners with names beginning with the letter J we do this:
<SPAN class="keyword token">SELECT</SPAN> <SPAN class="operator token">*</SPAN>
<SPAN class="keyword token">FROM</SPAN> parcel
<SPAN class="keyword token">WHERE</SPAN> owner <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'j%'</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">-- EXPLAIN QUERY PLAN: SCAN TABLE parcel</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Jack Dangermond"}</SPAN>
<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>Here, we observe that it found the right result, i.e. Jack Dangermond, however, the query needed to do a full table scan (i.e. SCAN TABLE parcel) to find that this was the only result.
Let's attempt to speed this up with an index and try again:
<SPAN class="keyword token">CREATE</SPAN> <SPAN class="keyword token">INDEX</SPAN> ix_parcel_owner <SPAN class="keyword token">ON</SPAN> parcel <SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">SELECT</SPAN> <SPAN class="operator token">*</SPAN>
<SPAN class="keyword token">FROM</SPAN> parcel
<SPAN class="keyword token">WHERE</SPAN> owner <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'j%'</SPAN>
<SPAN class="comment token">-- EXPLAIN QUERY PLAN: SCAN TABLE parcel</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Jack Dangermond"}</SPAN>
<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>Well, we got the right result, i.e. Jack Dangermond, but, why is it still doing a full table scan (SCAN TABLE parcel)?
Why didn't it use my index ix_parcel_owner?
The answer is SQLite, unlike other databases, implements LIKE as a case insensitive search so we got Jack Dangermond which begins with a capital J even though our search pattern was "j%" which begins with a lowercase J. The index that we created was a case sensitive index. LIKE ignored the index because it needed a case insensitive index.
To fix this, we try again. We create an index specific for case insensitive searches.
<SPAN class="keyword token">CREATE</SPAN> <SPAN class="keyword token">INDEX</SPAN> ix_parcel_owner_collate <SPAN class="keyword token">ON</SPAN> parcel <SPAN class="punctuation token">(</SPAN>owner <SPAN class="keyword token">COLLATE</SPAN> NOCASE<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">SELECT</SPAN> <SPAN class="operator token">*</SPAN>
<SPAN class="keyword token">FROM</SPAN> parcel
<SPAN class="keyword token">WHERE</SPAN> owner <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'j%'</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">-- EXPLAIN QUERY PLAN: SEARCH TABLE parcel USING COVERING INDEX ix_parcel_owner_collate (owner>? AND owner<?)</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Jack Dangermond"}</SPAN>
<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>There, we did it. The result is still Jack Dangermond, this time the index was used (SEARCH TABLE parcel USING COVERING INDEX ix_parcel_owner_collate). Take home message, COLLATE NOCASE is your friend.
Alternatives to LIKE for string contains searches
Now that we've got some success with LIKE, let's use it to find more things. Let's see if we can find all owners with the letter C anywhere in their name:
<SPAN class="keyword token">SELECT</SPAN> <SPAN class="operator token">*</SPAN>
<SPAN class="keyword token">FROM</SPAN> parcel
<SPAN class="keyword token">WHERE</SPAN> owner <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%c%'</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">-- EXPLAIN QUERY PLAN: SCAN TABLE parcel</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Jack Dangermond"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Tim Cook"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Mark Zuckerberg"}</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>Okay, we got the results we wanted. Jack Dangermond, Tim Cook and Mark Zuckerberg all have the letter C is their names. However, why are we back at full table scans (SCAN TABLE parcel)? Why isn't the index (ix_parcel_owner_collate) being used any more? That's because the B-Tree index being used works like how you look up names in a phone book. It works great if you have the starting letter(s) (i.e. divide the book in half, choose the half your letter is in, divide the book in half again).
However, because we don't have a starting letter, we can no longer use the phone book trick. We're back scanning every record in the table. The index we created is useless for this type of query.
So, what can we do? Well, there's a technique you can use but it requires rewriting the query, have a look at the following:
<SPAN class="keyword token">SELECT</SPAN> <SPAN class="operator token">*</SPAN><SPAN class="punctuation token">,</SPAN> INSTR<SPAN class="punctuation token">(</SPAN>LOWER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> LOWER<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'c'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> idx
<SPAN class="keyword token">FROM</SPAN> parcel<SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">-- EXPLAIN QUERY PLAN: SCAN TABLE parcel</SPAN>
<SPAN class="comment token">-- OUTPUT: {"idx":0,"owner":"Bill Gates"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"idx":0,"owner":"Steve Jobs"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"idx":3,"owner":"Jack Dangermond"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"idx":0,"owner":"Steve Wozniak"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"idx":5,"owner":"Tim Cook"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"idx":8,"owner":"Mark Zuckerberg"}</SPAN>
<SPAN class="keyword token">SELECT</SPAN> <SPAN class="operator token">*</SPAN>
<SPAN class="keyword token">FROM</SPAN> parcel
<SPAN class="keyword token">WHERE</SPAN> INSTR<SPAN class="punctuation token">(</SPAN>LOWER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> LOWER<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'c'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">-- EXPLAIN QUERY PLAN: SCAN TABLE parcel</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Jack Dangermond"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Tim Cook"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Mark Zuckerberg"}</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>What is happening is we're using a function INSTR which returns the position of a substring (i.e. the letter C) in a string (i.e. owner). If the substring doesn't exist, you will simply get 0. We use the LOWER function on both the letter C and the owner to make it a case insensitive search.
The result is still the same as the previous version involving LIKE, i.e. we are getting all owners with the letter C in their names. However, the query plan is still doing a full table scan (SCAN TABLE parcel).
So, what's the point?
Well, SQLite allows you to index expressions! Think of it like a pre-calculated column.
<SPAN class="keyword token">CREATE</SPAN> <SPAN class="keyword token">INDEX</SPAN> ix_parcel_owner_instr_c <SPAN class="keyword token">ON</SPAN> parcel <SPAN class="punctuation token">(</SPAN>INSTR<SPAN class="punctuation token">(</SPAN>LOWER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> LOWER<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'c'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN></SPAN>
The above statement will create an index. It may take some time. For example, if you had over 200000 records, that index may take several seconds to create. Consider that to be a good thing. Time spent here means the INSTR expression is being calculated for all records in the database once and only once. Every time we add a new record or modify an existing record, it will be the only time when that INSTR expression is calculated / recalculated.
That expression will never be recalculated at the time of the query. The query would just reuse the pre-calculated value that was stored in the index:
<SPAN class="keyword token">SELECT</SPAN> <SPAN class="operator token">*</SPAN>
<SPAN class="keyword token">FROM</SPAN> parcel
<SPAN class="keyword token">WHERE</SPAN> INSTR<SPAN class="punctuation token">(</SPAN>LOWER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> LOWER<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'c'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="comment token">-- EXPLAIN QUERY PLAN: SEARCH TABLE parcel USING INDEX ix_parcel_owner_instr_c (<expr>>?)</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Jack Dangermond"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Tim Cook"}</SPAN>
<SPAN class="comment token">-- OUTPUT: {"owner":"Mark Zuckerberg"}</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>The above confirms we are now using the index (SEARCH TABLE parcel USING INDEX ix_parcel_owner_instr_c).
Code Sample
This AppStudio code sample was used to generate all the SQL content in this blog:
<SPAN class="keyword token">import</SPAN> QtQuick <SPAN class="number token">2.7</SPAN>
<SPAN class="keyword token">import</SPAN> QtQuick<SPAN class="punctuation token">.</SPAN>Controls <SPAN class="number token">2.1</SPAN>
<SPAN class="keyword token">import</SPAN> ArcGIS<SPAN class="punctuation token">.</SPAN>AppFramework <SPAN class="number token">1.0</SPAN>
<SPAN class="keyword token">import</SPAN> ArcGIS<SPAN class="punctuation token">.</SPAN>AppFramework<SPAN class="punctuation token">.</SPAN>Sql <SPAN class="number token">1.0</SPAN>
App <SPAN class="punctuation token">{</SPAN>
id<SPAN class="punctuation token">:</SPAN> app
width<SPAN class="punctuation token">:</SPAN> <SPAN class="number token">800</SPAN> <SPAN class="operator token">*</SPAN> AppFramework<SPAN class="punctuation token">.</SPAN>displayScaleFactor
height<SPAN class="punctuation token">:</SPAN> <SPAN class="number token">640</SPAN> <SPAN class="operator token">*</SPAN> AppFramework<SPAN class="punctuation token">.</SPAN>displayScaleFactor
property string logText<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">""</SPAN>
Flickable <SPAN class="punctuation token">{</SPAN>
id<SPAN class="punctuation token">:</SPAN> flickable
anchors<SPAN class="punctuation token">.</SPAN>fill<SPAN class="punctuation token">:</SPAN> parent
anchors<SPAN class="punctuation token">.</SPAN>margins<SPAN class="punctuation token">:</SPAN> <SPAN class="number token">10</SPAN>
contentWidth<SPAN class="punctuation token">:</SPAN> textArea<SPAN class="punctuation token">.</SPAN>width
contentHeight<SPAN class="punctuation token">:</SPAN> textArea<SPAN class="punctuation token">.</SPAN>height
clip<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">true</SPAN>
TextArea <SPAN class="punctuation token">{</SPAN>
id<SPAN class="punctuation token">:</SPAN> textArea
width<SPAN class="punctuation token">:</SPAN> flickable<SPAN class="punctuation token">.</SPAN>width
wrapMode<SPAN class="punctuation token">:</SPAN> Text<SPAN class="punctuation token">.</SPAN>WrapAtWordBoundaryOrAnywhere
selectByMouse<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">true</SPAN>
text<SPAN class="punctuation token">:</SPAN> logText
<SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>
SqlDatabase <SPAN class="punctuation token">{</SPAN>
id<SPAN class="punctuation token">:</SPAN> db
databaseName<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">":memory:"</SPAN>
<SPAN class="punctuation token">}</SPAN>
Component<SPAN class="punctuation token">.</SPAN>onCompleted<SPAN class="punctuation token">:</SPAN> <SPAN class="punctuation token">{</SPAN>
db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">open</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN> <SPAN class="punctuation token">[</SPAN>
<SPAN class="string token">"CREATE TABLE parcel"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"("</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">" owner TEXT"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">");"</SPAN>
<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">join</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"INSERT INTO parcel (owner) values ('Bill Gates');"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"INSERT INTO parcel (owner) values ('Steve Jobs');"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"INSERT INTO parcel (owner) values ('Jack Dangermond');"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"INSERT INTO parcel (owner) values ('Steve Wozniak');"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"INSERT INTO parcel (owner) values ('Tim Cook');"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"INSERT INTO parcel (owner) values ('Mark Zuckerberg');"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN> <SPAN class="punctuation token">[</SPAN>
<SPAN class="string token">"SELECT *"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"FROM parcel"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"WHERE owner LIKE 'j%';"</SPAN>
<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">join</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"CREATE INDEX ix_parcel_owner ON parcel (owner);"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN> <SPAN class="punctuation token">[</SPAN>
<SPAN class="string token">"SELECT *"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"FROM parcel"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"WHERE owner LIKE 'j%';"</SPAN>
<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">join</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"CREATE INDEX ix_parcel_owner_collate ON parcel (owner COLLATE NOCASE);"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN> <SPAN class="punctuation token">[</SPAN>
<SPAN class="string token">"SELECT *"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"FROM parcel"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"WHERE owner LIKE 'j%';"</SPAN>
<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">join</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN> <SPAN class="punctuation token">[</SPAN>
<SPAN class="string token">"SELECT *"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"FROM parcel"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"WHERE owner LIKE '%c%';"</SPAN>
<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">join</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN> <SPAN class="punctuation token">[</SPAN>
<SPAN class="string token">"SELECT *, INSTR(LOWER(owner), LOWER('c')) idx"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"FROM parcel;"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">join</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN> <SPAN class="punctuation token">[</SPAN>
<SPAN class="string token">"SELECT *"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"FROM parcel"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"WHERE INSTR(LOWER(owner), LOWER('c')) > 0;"</SPAN>
<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">join</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"CREATE INDEX ix_parcel_owner_instr_c ON parcel (INSTR(LOWER(owner), LOWER('c')));"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN> <SPAN class="punctuation token">[</SPAN>
<SPAN class="string token">"SELECT *"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"FROM parcel"</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"WHERE INSTR(LOWER(owner), LOWER('c')) > 0;"</SPAN>
<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">join</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN>
<SPAN class="keyword token">function</SPAN> <SPAN class="token function">exec</SPAN><SPAN class="punctuation token">(</SPAN>sql<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
logText <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> <SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">;</SPAN>
logText <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> sql <SPAN class="operator token">+</SPAN> <SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> explain <SPAN class="operator token">=</SPAN> db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">query</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"EXPLAIN QUERY PLAN "</SPAN> <SPAN class="operator token">+</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="operator token">!</SPAN>explain<SPAN class="punctuation token">.</SPAN>error <SPAN class="operator token">&&</SPAN> explain<SPAN class="punctuation token">.</SPAN><SPAN class="token function">first</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
logText <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> <SPAN class="string token">"-- EXPLAIN QUERY PLAN: %1\n"</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">arg</SPAN><SPAN class="punctuation token">(</SPAN>explain<SPAN class="punctuation token">.</SPAN>values<SPAN class="punctuation token">.</SPAN>detail<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
explain<SPAN class="punctuation token">.</SPAN><SPAN class="token function">finish</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN>
<SPAN class="keyword token">var</SPAN> query <SPAN class="operator token">=</SPAN> db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">query</SPAN><SPAN class="punctuation token">(</SPAN>sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN>query<SPAN class="punctuation token">.</SPAN>error<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
logText <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> query<SPAN class="punctuation token">.</SPAN>error<SPAN class="punctuation token">.</SPAN>databaseText<SPAN class="punctuation token">;</SPAN>
logText <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> query<SPAN class="punctuation token">.</SPAN>error<SPAN class="punctuation token">.</SPAN>driverText<SPAN class="punctuation token">;</SPAN>
logText <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> <SPAN class="token function">qsTr</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"NativeErrorCode: %1"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">arg</SPAN><SPAN class="punctuation token">(</SPAN>query<SPAN class="punctuation token">.</SPAN>error<SPAN class="punctuation token">.</SPAN>nativeErrorCode<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
logText <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> <SPAN class="token function">qsTr</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"ErrorType: %1"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">arg</SPAN><SPAN class="punctuation token">(</SPAN>query<SPAN class="punctuation token">.</SPAN>error<SPAN class="punctuation token">.</SPAN>type<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">return</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN>
<SPAN class="keyword token">var</SPAN> ok <SPAN class="operator token">=</SPAN> query<SPAN class="punctuation token">.</SPAN><SPAN class="token function">first</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">while</SPAN> <SPAN class="punctuation token">(</SPAN>ok<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
logText <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> <SPAN class="string token">"-- OUTPUT: %1\n"</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">arg</SPAN><SPAN class="punctuation token">(</SPAN>JSON<SPAN class="punctuation token">.</SPAN><SPAN class="token function">stringify</SPAN><SPAN class="punctuation token">(</SPAN>query<SPAN class="punctuation token">.</SPAN>values<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
ok <SPAN class="operator token">=</SPAN> query<SPAN class="punctuation token">.</SPAN><SPAN class="token function">next</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN>
query<SPAN class="punctuation token">.</SPAN><SPAN class="token function">finish</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>Summary
If speed matters to you, you cannot just create an index and just leave it there.
You need to check whether your queries use the index with EXPLAIN QUERY PLAN. If your index isn't being used, look at your query. Look at your WHERE clause. Think of what is happening there. Rewrite your WHERE clause if necessary. Create indexes that matches your WHERE clause.
If necessary, be prepared to index on expressions. Don't go overboard, we didn't create an index for every letter of the alphabet. That wasn't in our requirements today. We only wanted to search for the letter C and do that better. Over time, requirements changes. We probably will become disinterested in search for the letter C, then, feel free to drop that index. However, if a new criteria becomes more important, e.g. we want to search for all owners with Jack in the first, middle or last name, then, we will create an index to help with that purpose.
