Hi. I'm not able to reproduce your problem, can you post the creation table script?
Att, Fred 2012/4/26 Frederico <zepf...@gmail.com> > Hi. This is because H2 uses only one index per logical table. Take a look > on using multiples indexes here: > http://www.h2database.com/html/performance.html#explain_plan > > Att, > > Fred > > Enviado via iPad > > Em 26/04/2012, às 09:30, Steve McLeod <steve.mcl...@gmail.com> escreveu: > > Hi Thomas, > > It is useful to put EXPLAIN ANALYZE before your query to see what's going > on. It will show you which index is picked, and whether a table scan is > necessary: > > explain analyze SELECT * FROM TRIPPLETCOUNT > WHERE RELATION = 'REQUESTED' > AND ( LEFT = 'sb_1909322' OR RIGHT= 'sb_1909322') > ORDER BY COUNT DESC; > PLAN > <http://192.168.1.214:53194/query.do?jsessionid=67191d707d067a046d6026c5056ee9be#>SELECT > TRIPPLETCOUNT.LEFT, > TRIPPLETCOUNT.RIGHT, > TRIPPLETCOUNT.RELATION, > TRIPPLETCOUNT.COUNT > FROM PUBLIC.TRIPPLETCOUNT > */* PUBLIC.TRIPPLETCOUNT.tableScan */* > /* scanCount: 1 */ > WHERE (RELATION = 'REQUESTED') > AND ((LEFT = 'sb_1909322') > OR (RIGHT = 'sb_1909322')) > ORDER BY 4 DESC(1 row, 0 ms) > > > explain analyze SELECT * FROM TRIPPLETCOUNT > WHERE RELATION = 'REQUESTED' > AND LEFT = 'sb_1909322' > UNION > SELECT * FROM TRIPPLETCOUNT > WHERE RELATION = 'REQUESTED' > AND RIGHT = 'sb_1909322' > ORDER BY COUNT DESC; > PLAN > <http://192.168.1.214:53194/query.do?jsessionid=67191d707d067a046d6026c5056ee9be#>(SELECT > DISTINCT > TRIPPLETCOUNT.LEFT, > TRIPPLETCOUNT.RIGHT, > TRIPPLETCOUNT.RELATION, > TRIPPLETCOUNT.COUNT > FROM PUBLIC.TRIPPLETCOUNT > * /* PUBLIC.INDEX_LEFT: LEFT = 'sb_1909322' */* > /* scanCount: 1 */ > WHERE (RELATION = 'REQUESTED') > AND (LEFT = 'sb_1909322')) > UNION > (SELECT DISTINCT > TRIPPLETCOUNT.LEFT, > TRIPPLETCOUNT.RIGHT, > TRIPPLETCOUNT.RELATION, > TRIPPLETCOUNT.COUNT > FROM PUBLIC.TRIPPLETCOUNT > */* PUBLIC.INDEX_RIGHT: RIGHT = 'sb_1909322' */* > /* scanCount: 1 */ > WHERE (RELATION = 'REQUESTED') > AND (RIGHT = 'sb_1909322')) > ORDER BY 4 DESC > > > On Thursday, 26 April 2012 12:29:44 UTC+2, Thomas Egense wrote: >> >> I tested that the query time with the OR-query matches a full table >> scan and this is the simplest explanation also. >> >> On Apr 26, 11:45 am, Steve McLeod <steve.mcl...@gmail.com> wrote: >> > My understanding is that H2 chooses at most one index to use per SELECT >> > statement. With your OR example, this will therefore require a complete >> > table scan to satisfy one side of the OR statement. >> > >> > I wonder how other database engines deal with this. Do they use >> multiple >> > indexes per SELECT? Or do they rewrite the SQL statement to become a >> UNION >> > statement? >> > >> > >> > >> > >> > >> > >> > >> > On Wednesday, 25 April 2012 09:33:41 UTC+2, Thomas Egense wrote: >> > >> > > I am using the latest version of H2: 1.3.166 >> > > Maybe this issue is known, but for most other DB-products it is >> > > counterintuitive. >> > >> > > The table has about 6M rows. Table has 4 >> > > Columns(left,right,relation,**count) and each have their own index. >> > > SQL1 and SQL2 below are result-set identical except the order is >> > > slightly different for same value of COUNT. >> > > And normally for performance you would use SQL1. >> > >> > > SQL1: Takes 1 second+. Sometimes several seconds. >> > > SQL2: Takes around 2 milis. >> > >> > > SQL1: >> > > SELECT * FROM TRIPPLETCOUNT >> > > WHERE RELATION = 'REQUESTED' >> > > AND ( LEFT = 'sb_1909322' OR RIGHT= 'sb_1909322') >> > > ORDER BY COUNT DESC >> > >> > > SQL2: >> > > SELECT * FROM TRIPPLETCOUNT >> > > WHERE RELATION = 'REQUESTED' >> > > AND LEFT = 'sb_1909322' >> > > UNION >> > > SELECT * FROM TRIPPLETCOUNT >> > > WHERE RELATION = 'REQUESTED' >> > > AND RIGHT = 'sb_1909322' >> > > ORDER BY COUNT DESC > > -- > 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/-/yl-Th51GZAsJ. > To post to this group, send email to h2-database@googlegroups.com. > To unsubscribe from this group, send email to > h2-database+unsubscr...@googlegroups.com. > 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 h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
