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 > 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 > (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.
