Sorting with index

[Petr Holik]

Hello,

I am little bit unsure when index is used for sorting and when not. I
have two almost same SQL first uses sort using index and second not.
The only difference is in DESC keyword.

the SQL is as follows:

select * from bills where closedtime < '2012-06-08 12:04:36.343 ' AND
CLOSED = TRUE order by closedtime limit 1
select * from bills where closedtime < '2012-06-08 12:04:36.343 ' AND
CLOSED = TRUE order by closedtime DESC limit 1

of course i can supply explain analyze if needed. Second query on same
data is about 10 - 100 times slower(does not use index for sorting)

Where is mistake or am I missing something?

Sincerely Petr Holik

[Noel Grandin]

H2 doesn't support stepping through an index backwards, you would need
to create a reverse index to support that.

Something like :

create index idx_id_desc on test(id desc)

[patrice henrio]

I can give my experience  (not big, 2 or 3 M) with à complex request sorting on a column : without index more than 15 minutes, with index 7 seconds. 

2012/6/8 Noel Grandin <noelg...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

--

Si vous vous intéressez à l'histoire, vous pouvez parcourir mon site : http://histoiremondiale.free.fr

[Petr Holik]

Hello Noel, 

thanks for, reply your sugestion works. BUT

I do not see the reason why H2 does not support this? Binary tree which is used in H2 can support stepping backward and forward, am I right?

Should not be this enhancement request? It is very often request in web application that user can user sort data ASC or DESC...

Or there should be updated documentation in crate index syntax. There is no word about fact that index can be crated on DESC column.

Petr

[Rami Ojares]

Something I have been also wondering.

- rami

[Noel Grandin]

On Fri, Jun 8, 2012 at 6:11 PM, Petr Holik <hol...@gmail.com> wrote:
> Should not be this enhancement request? It is very often request in web
> application that user can user sort data ASC or DESC...
>

Patches are welcome, the code is open-source.....

> Or there should be updated documentation in crate index syntax. There is no
> word about fact that index can be crated on DESC column.
>

I will see what I can do about updating the documentation,

[Thomas Mueller]

Hi,

Reading in descending order is possible now, but I guess it will be much slower.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database...@googlegroups.com.

[Rami Ojares]

Hooray!
Since which version?
167 or an upcoming?

- Rami

[Thomas Mueller]

Hi,

Hooray!
Since which version?
167 or an upcoming?

It's possible since quite a long time actually (function Cursor.previous()). However, this is not used internally to support "order by desc". It is only used for a few edge cases (select max(...) if there are null entries at the end). It would not perform well for regular index scans I believe.

Regards,

Thomas