Hi Thomas, Even with your database file, the performance using OR can't be improved. You've hit a limit of H2 - a maximum of 1 index per SELECT. An index that contains multiple fields still won't help, because of the way H2 indexes work. Let me explain:
If your index is on (field1, field2, field3) then this index can be applied if the where clause refers to either: * just field1 * field1 + field2 * field1 + field2 + field3 The index can't be used if you only refer to field2, or field3, or a combination of field2 and field3. This is the case only when the WHERE clause is using AND, rather than OR The OR in your where clause complicates this further. You should consider that your where clause is effectively two where clauses, one for field1 and one for field2. I looked back at the query in your original post. Your where clause is: WHERE RELATION = 'REQUESTED' AND ( LEFT = 'sb_1909322' OR RIGHT= 'sb_1909322') Performance tip 1: If only a small percentage of rows have RELATION = 'REQUESTED' then you could have an index on (RELATION, LEFT). This would undoubtedly lead to better performance than you have now. Performance tip 2: If possible, can you use an INTEGER instead of a VARCHAR for RELATION? This will make your row data smaller and therefore marginally faster to read I'd imagine. On Thursday, 3 May 2012 17:57:17 UTC+2, Thomas Egense wrote: > > Any news from dissecting the databasefile I gave you? > > From, > Thomas > > > On May 2, 10:58 am, Noel Grandin <[email protected]> wrote: > > Thomas, your test case cannot be correct - notice that they are both > > reading the same amount of data. The only reason the second is faster is > > because the data is cache-hot after you have run the first query. > > > > On 2012-05-01 07:50, Thomas Egense wrote: > > > > > > > > > > > > > > > > > /* > > > total: 67483 > > > TRIPPLETCOUNT.RELATION_IN read: 17919 (26%) > > > TRIPPLETCOUNT.TRIPPLETCOUNT_DATA read: 49564 (73%) > > > */ > > > /* > > > total: 67483 > > > TRIPPLETCOUNT.RELATION_IN read: 17919 (26%) > > > TRIPPLETCOUNT.TRIPPLETCOUNT_DATA read: 49564 (73%) > > > */ -- 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/-/-q94--6nesQJ. 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.
