Hello, The reason I want to use idx_type is based on some articles about indexing group by. Here's one of them: http://use-the-index-luke.com/sql/sorting-grouping/indexed-group-by
And it does seems to increase the performance on my experiment in H2 web console with 1 million records. Please try run these queries in H2 web console (will create 1 million rows). CREATE TABLE `agent_cfg` ( `AGENT_ID` int(11) NOT NULL auto_increment, `DELETED` int(1) NOT NULL default '0', `ORG_ID` int(11) NOT NULL default '0', `PHYSICAL_CLASS_TYPE` tinyint(3) NOT NULL default '0', PRIMARY KEY (`AGENT_ID`) ); CREATE INDEX IDX_AGENT_CFG_CLASS_SUM ON agent_cfg(PHYSICAL_CLASS_TYPE, DELETED, ORG_ID); CREATE INDEX IDX_AGENT_CFG_ORG_SUM ON agent_cfg(ORG_ID, DELETED); @loop 100000 insert into agent_cfg values (1 + ?,0,0,0); SELECT ac.physical_class_type, count(*) FROM AGENT_CFG ac where ac.deleted = 0 and ac.org_id = 0 group by ac.physical_class_type order by ac.physical_class_type; DROP INDEX IDX_AGENT_CFG_ORG_SUM; SELECT ac.physical_class_type, count(*) FROM AGENT_CFG ac where ac.deleted = 0 and ac.org_id = 0 group by ac.physical_class_type order by ac.physical_class_type; On my test the second select is faster because one of the index has been dropped. Thanks! > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
