Can this select query be optimized more on a mobile device?

Question

We have a C# mobile application with SQLite database. We are having a larger inventory database, such as 30k or 100k items. The database file is 12MB on a flash memory card.

Running a simpler SELECT query with limit takes 10-15 seconds.

select id,invitem,invid,cost from inventory 
   where itemtype = 1 and 
   (invitem like '%5204d%' or invid like '%5204d%') 
   limit 25

sometimes a category is involved too,

select id,invitem,invid,cost from inventory 
   where itemtype = 1 and 
   categoryid=147 and 
   (invitem like '%5204d%' or invid like '%5204d%')  
   limit 25

Indexes are created on:

cmd.CommandText = "CREATE INDEX IF NOT EXISTS idx_inventory_categoryid ON " + this.TableName + " (categoryid);";
cmd.ExecuteNonQuery();

cmd.CommandText = "CREATE INDEX IF NOT EXISTS idx_inventory_itemtype ON " + this.TableName + " (itemtype);";
cmd.ExecuteNonQuery();

cmd.CommandText = "CREATE INDEX IF NOT EXISTS idx_inventory_invitem ON " + this.TableName + " (invitem);";
cmd.ExecuteNonQuery();

Those two fields in Like are VARCHAR, the others are NUMERIC.

Can this select query be optimized more on a mobile device?

Answer 1

You can use EXPLAIN to check whether the indices are actually used. I would guess that the wildcard at the beginning of like '%5204d%' disables any use of indices on invitem.

Answer 2

The problem is the initial % in your where..like clause. The index cannot be used in theis query, so a table scan is the only way it can be done. Adding the category id will help - at least it can use that inxdex.