I am not sure what I said to upset you. I don't know the answer. This is why I am asking. I have now tried with various indexes 1) CREATE INDEX index1 ON myTable(id) 2) CREATE INDEX index1 ON myTable(x) 3) CREATE INDEX index1 ON myTable(id, x)
I have made a testbed where I insert 1,000,000 records and do my select with various combinations of indexes. Here are the results sorted by speed: Index 1, 2, 3: 13516 ms index 1, 3: 3560 ms index 1, 2: 3716 ms index 2, 3: 3285 ms index 1: 2776 ms No indexes: 2637 ms I am really surprised by the results. For some reason I get the fastest query with no indexes at all. On Friday, November 2, 2012 9:54:10 AM UTC+1, Noel Grandin wrote: > > What is it about people, that they show up here asking for help, and then > somebody gives them a suggestion and they tell me they know better, without > even bothering to try it out. > > If you already know the answer, then why are you asking? > > On 2012-11-02 10:48, Jan Møller wrote: > > I tried that initially, but that does not seem to work > In the article: > http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ > He claims that you should have the index cover both the WHERE clause and > the ORDER BY clause to get good performance. This is for MySql, so I don't > know if it applies to H2. > > On Friday, November 2, 2012 9:29:45 AM UTC+1, Noel Grandin wrote: >> >> you need >> >> CREATE INDEX index2 ON myTable(x) >> >> in order to make the ORDER BY run fast. >> >> On 2012-11-02 09:50, Jan M�ller wrote: >> > CREATE INDEX index2 ON myTable(id, x) >> >> -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/h2-database/-/lk-D69JsaTQJ. > To post to this group, send email to [email protected]<javascript:> > . > To unsubscribe from this group, send email to > [email protected] <javascript:>. > 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 view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/Os8cIb5OtNAJ. 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.
