And just as a follow up: on the auto-generated serial id column, I don't need to explicitly do a "CREATE INDEX .." on it, do I?
Thanks. -- Alessio Pace. On Sun, Oct 10, 2010 at 9:39 AM, Alessio Pace <[email protected]>wrote: > 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.
