Hi Thomas,
thanks for your answer. I'm trying to understand what you mean by: "it's
better to use the auto-generated id instead of the indexed timestamp".
If my understanding is correct, the auto-generated id is like: 0, 1, 2, 3,
..... for each row I insert.
Assuming the rows are inserted by ascending timestamp order (the other
column I wanted to use), you mean that I could avoid inserting this second
column, and then later do my SELECTs by simply "mapping" the id column to
the corresponding timestamp value that would have appeared there.
Example, assuming the timestamp granularity is 100 ms:
| id (auto-generated) | timestamp |
0 0
1 100
2 200
... ...
Then if in my app I would like to SELECT from timestamp 0 to timestamp 1000,
I would do then map it to a "SELECT * from my_table where id >= 0 AND id <=
10". Is this what you meant?
Doing like this, I could completely get rid of the timestamp column in the
schema (and so, also of the index built on it).
Thanks in advance for any comment on it.
--
Alessio Pace.
On Fri, Oct 8, 2010 at 9:20 PM, Thomas Mueller <[email protected]
> wrote:
> Hi,
>
> I think your solution is very good. I would do the same.
>
> Maybe it's better to use the auto-generated id instead of the indexed
> timestamp. Reason: the id is the "clustered index", meaning no index
> lookup is required. In theory it should speed up querying a bit (maybe
> 10% or so). Also you may not need an index for the timestamp, which
> would increase insert performance. Do you have a lot of rows with the
> same timestamp?
>
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected]<h2-database%[email protected]>
> .
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
