Hallo I would need a simple, reproducible test case.
German is OK, ich spreche Deutsch. Ich bräuchte einen einfachen, reproduzierbaren Test-Case. Ich habe gesehen dass einige "break" verschwunden sind, aber ich denke diese werden benötigt. Bei einem Mehrspalten-Index ist für Bereichs-Abfragen bei der ersten Spalte Schluss. Beispiel: (1, a) (1, b) (2, a) (2, b) .... Eine Abfrage "> (1 d)" wird recht viele Datensätze betreffen, hier kann man die zweite Spalte nicht berücksichtigen denke ich. Regards, Thomas On Wed, Jan 29, 2014 at 11:21 AM, <[email protected]> wrote: > In the patch is not only double changed, but I have replaced some "break". > So it will calculate entire index, not just the beginning. > > Ich spreche kein Englisch und kann deshalb nicht so gut erklären. > > Am Dienstag, 28. Januar 2014 19:04:45 UTC+1 schrieb Thomas Mueller: >> >> 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]. >>> 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. >>> >> -- > 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. > -- 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.
