Condition expression optimization

[scomouse]

when testing my sql to understand how an index of H2 works, i find
some unnecessary loop . The sql likes that :

SELECT * FROM TEST WHERE AGE > 0

if the most row's value on the column AGE is bigger than 0, H2 still
search and check them. the related code (see the file Select.java,
method queryFlat() ) :

512 while (topTableFilter.next()) {
513 setCurrentRowNumber(rowNumber + 1);
514 if (condition == null ||
Boolean.TRUE.equals(condition.getBooleanValue(session))) {

before the code , i still find condtion optimize in method prepare() :

796 if (condition != null) {
797 condition = condition.optimize(session);

but no optimization occurs in this place,

i think the condition expression could optimize like : AGE >=1 , to
avoid unnecessary loop in method queryFlat().

[Thomas Mueller]

Hi,

That's true, in this case the database scans though too many rows. The
best solution would be to support index lookup for rows higher than a
value. This is currently supported by regular database indexes, but
not when using MVCC or in-memory indexes. This will also require some
changes elsewhere. I will add this to the roadmap, as I believe it's
currently not the most important improvement (for example, in-place
updates are more important).

Of course the trick to change AGE > 0 to AGE >= 1 works, but adding
that to the database engine is a bit tricky (it only works for integer
data types, and only for constants but not parameters as there are
problems with Integer.MAX_VALUE). Of course, a workaround is to use
this trick within the application :-)

Regards,
Thomas

[Maaartin G]

On Friday, July 15, 2011 6:02:25 PM UTC+2, Thomas Mueller wrote:

Of course the trick to change AGE > 0 to AGE >= 1 works, but adding
that to the database engine is a bit tricky (it only works for integer
data types, and only for constants but not parameters as there are
problems with Integer.MAX_VALUE). Of course, a workaround is to use
this trick within the application :-)

Couldn't you simply replace "age > 0" by "age >= 0 and age <> 0" in every case? It works also for non-integers and the inequality should be able to use the index while the equality should take care for the border cases... Regards, Maaartin.

[Thomas Mueller]

Hi,

> Couldn't you simply replace "age > 0" by "age >= 0 and age <> 0" in every
> case?

Do you mean in the database? What problem would it solve? It wouldn't
improve performance.

Regards,
Thomas

[Maaartin G]

On Jul 24, 7:53 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

It was meant as an replacement for what you wrote

> Of course the trick to change AGE > 0 to AGE >= 1 works, but adding
> that to the database engine is a bit tricky (it only works for integer
> data types, and only for constants but not parameters as there are
> problems with Integer.MAX_VALUE).

(since it has problem neither with non-integers nor with
Integer.MAX_VALUE), but I probably misunderstood this thread.