Hi Thomas The H2 version I'm using is: 1.3.158 (I'm using h2-1.3.158.jar)
I tried your test; these are results: create table test(id int primary key, name varchar(255)); Table "TEST" already exists; SQL statement: create table test(id int primary key, name varchar(255)) [42101-158]<http://localhost:8083/query.do?jsessionid=bd989f4947ccc7c7351ef05fb64341ae#> 42S01/42101 (Aiuto)<http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c42101> create index idx_name on test(name); Index "IDX_NAME" already exists; SQL statement: create index idx_name on test(name) [42111-158]<http://localhost:8083/query.do?jsessionid=bd989f4947ccc7c7351ef05fb64341ae#> 42S11/42111 (Aiuto)<http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c42111> insert into test select x, x/10000 from system_range(1, 1000000); Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.TEST(ID)"; SQL statement: insert into test select x, x/10000 from system_range(1, 1000000) [23505-158]<http://localhost:8083/query.do?jsessionid=bd989f4947ccc7c7351ef05fb64341ae#> 23505/23505 (Aiuto)<http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c23505> explain analyze select name, count(name) from test group by name; PLAN <http://localhost:8083/query.do?jsessionid=bd989f4947ccc7c7351ef05fb64341ae#> SELECT NAME, COUNT(NAME) FROM PUBLIC.TEST /* PUBLIC.IDX_NAME */ /* scanCount: 1000001 */ GROUP BY NAME /* group sorted */(1 riga, 219 ms) explain analyze select name, count(id) from test group by name; PLAN <http://localhost:8083/query.do?jsessionid=bd989f4947ccc7c7351ef05fb64341ae#> SELECT NAME, COUNT(ID) FROM PUBLIC.TEST /* PUBLIC.IDX_NAME */ /* scanCount: 1000001 */ GROUP BY NAME /* group sorted */ /* total: 9807 TEST.IDX_NAME read: 918 (9%) TEST.TEST_DATA read: 8889 (90%) */(1 riga, 7250 ms) I can see that the IDX_NAME is used in your test case....why isn't in my case? May this be due to my index name (maybe too long)? But i can see it inside the indexes table.... Il giorno 28 ottobre 2011 11:09, Thomas Mueller < [email protected]> ha scritto: > Hi, > > If there is an index on this column, then it should be used. I can't > reproduce the problem. Using 'count(HDR_EVENT_NUMBER)' is a very good > idea by the way, in theory H2 could do that automatically. What > version of H2 do you use? (For older versions, you may need to run > 'analyze' manually.) Could you run "script nodata" and post the > result? My test case is: > > drop table test; > create table test(id int primary key, name varchar(255)); > create index idx_name on test(name); > insert into test select x, x/10000 from system_range(1, 1000000); > explain analyze select name, count(name) from test group by name; > explain analyze select name, count(id) from test group by name; > > SELECT > NAME, > COUNT(NAME) > FROM PUBLIC.TEST > /* PUBLIC.IDX_NAME */ > /* scanCount: 1000001 */ > GROUP BY NAME > /* group sorted */ > /* > total: 6720 > TEST.IDX_NAME read: 6720 (100%) > */ > > Regards, > Thomas > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > 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. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. 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.
