Hi, OK I see. However, the patch doesn't seem to help here. I'm afraid the optimizer of H2 isn't smart enough to use the right plan in your case.
I think that one query is faster than the other is more or less coincidence, as the number of reads seem to be the same. Regards, Thomas On Tuesday, January 28, 2014, Pandu Purnama <[email protected]> wrote: > 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] <javascript:_e({}, > 'cvml', 'h2-database%[email protected]');>. > To post to this group, send email to > [email protected]<javascript:_e({}, 'cvml', > '[email protected]');> > . > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- 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.
