[odb-users] Index Pragmas DO NOT support index on expressions

[Reilly He]

Dear All,

Recently I`m focusing on query optimization on our own project.

1. Env
The general environment is:

   - *Compiler*: CXX11
   - *OS*: Android&IOS
   - *Inner DB*: SQLite


2. Background information
Here is basically the query looks like this:

> *SELECT* * *FROM* `Post`
> *WHERE* `group` = 1178615814 AND `isDirty` = 0 AND `isDeactivated` = 0
> *ORDER BY *
> `id` < 0 *DESC*,
> `creationTime` *DESC*
> *LIMIT* 20
> *OFFSET* 0
>

The query intends to look for at most 20 posts in a certain group/tribe in
our db, and especially those posts with negative ids on the *first*, then
the positive ids on the *latter*, and for these 2 groups, we sort it on
`creationTime` respectively.

In order to fully optimize this query, we need to bypass the additional
Temp-BTree sorting on `creationTime` as well as the `id < 0` part.
Since we are sorting based on the return of `id<0` (which is either 0 or
1), creating an index on data member `id` will not help.

We think about several solutions on this:

   - Creating a new data member to interpret the negative or positive id
   situation: but that relies on a heavy data migration
   - Using 2 queries on (id < 0, id > 0), and then try to merge it: that
   compromises our LIMIT and OFFSET result
   - Using something like CASE/WHEN to group the data: that will be even
   harder to optimize
   - Using Index on expression: since SQLite supports it
   <https://www.sqlite.org/expridx.html>

Obviously, *the index on expression solution is the best one*.

3. Problems
But I found it really hard to create this index in the context of ODB index
pragmas.
I looked through the manual:
https://www.codesynthesis.com/products/odb/doc/manual.xhtml#14.7
And I did not find any solution.

*So, can you guys tell me how to create an index based on member expression
in ODB? *
*or does ODB support that?*

So if ODB does not support creating indexes on an expression, I can do that
on my own using SQLite CREATE INDEX pragma.
But, this is not included in the ODB migration, which leaves a very
unstable point in our project. Since if somebody in the future changes the
schema in the Post table, this part might fail.

*So, can you guys tell me how to adopt this manual changes into the ODB
migration? *


I have been stuck in this for several days. I would appreciate it a lot if
you guys give me some suggestions. Thanks a lot.

Best regards,
Reilly He