Hello, I've following table: 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`) );
And 2 indices: 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); When I run this query: 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 Using EXPLAIN, I can see that it use IDX_AGENT_CFG_ORG_SUM index which takes 600 ms of execution time. I was expecting that query to use IDX_AGENT_CFG_CLASS_SUM. If I drop IDX_AGENT_CFG_ORG_SUM, then it correctly use IDX_AGENT_CFG_CLASS_SUM index which is faster (80 ms). The problem is, I can't drop IDX_AGENT_CFG_ORG_SUM since I need it for another query: SELECT o.org_id, count(ac.org_id) from organization o left join agent_cfg ac on o.org_id = ac.org_id and ac.deleted = 0 group by o.org_id order by o.org_id Is there a way to manually choose index so that I can make the first query faster? I'm using version 1.3.175 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.
