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 <[email protected]> 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 [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.
