Hello Adriano The problem is that I made the mistake of assuming that that the utf8 collations where mature, so now I have this problem in production databases, maybe there must by a warning in the documentation about of the shortcomings of the collations unicode_ci and unicode_ci_ai.
I changed most of indexes to unique by adding the primary key in the end, as they are numeric, in that case the querys (varchar,varchar,numeric unique) work as expected, but with foreign keys there isn't that option. regards Luis Forra --- Adriano dos Santos Fernandes <[email protected]> escreveu no dia quinta, 18/10/2018 à(s) 01:49: > Please check this recent discussion, that internally is about the same > thing: > > http://tracker.firebirdsql.org/browse/CORE-5940 > > Also, please change UNICODE_CI_AI to WIN_PTBR (with WIN1252 charset) in > your test. I believe the problem will not show on this condition, as > it's a MULTI-LEVEL=0 collation. > > > Adriano > > > On 17/10/2018 15:02, Luis Forra wrote: > > With unicode there is 2 fetchs in the detail table, with unicode_ci_ai > > it fetchs 2000, problem is that with millions of records in there is > > millions of fetches. collation unicode don't have the same problem. > > > > Thank you > > > > regards > > > > Mark Rotteveel <[email protected] <mailto:[email protected]>> > > escreveu no dia quarta, 17/10/2018 à(s) 18:57: > > > > Can you please explicitly describe the problem, instead of expecting > us > > to interpret the output and try to figure out what problem you see > here? > > > > Mark > > > > On 17-10-2018 19:47, Luis Forra wrote: > > > After discussing in firebird-suport I believe that this is > > relevant to > > > this list > > > > > > The databases that I have migrate to utf8 with colation > unicode_ci_ai > > > are much slower in use, the problem is the indexes with various > > varchar > > > fields. > > > > > > example of the problem > > > > > > > > > CREATE TABLE M_UNICODE ( > > > S1 VARCHAR(10) NOT NULL COLLATE UNICODE, > > > S2 VARCHAR(10) NOT NULL COLLATE UNICODE > > > ); > > > CREATE TABLE D_UNICODE ( > > > S1 VARCHAR(10) NOT NULL COLLATE UNICODE, > > > S2 VARCHAR(10) NOT NULL COLLATE UNICODE > > > ); > > > CREATE TABLE M_CI_AI ( > > > S1 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI, > > > S2 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI > > > ); > > > > > > CREATE TABLE D_CI_AI ( > > > S1 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI, > > > S2 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI > > > ); > > > INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'A'); > > > INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'B'); > > > > > > INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'A'); > > > INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'B'); > > > > > > set term ^ ; > > > > > > execute block > > > as > > > declare variable i bigint = 1000; > > > begin > > > while (i > 0) do > > > begin > > > insert into d_unicode (s1,s2) values ('A','A'); > > > insert into d_ci_ai (s1,s2) values ('A','A'); > > > i = i-1; > > > end > > > insert into d_unicode (s1,s2) values ('A','B'); > > > insert into d_ci_ai (s1,s2) values ('A','B'); > > > end^ > > > > > > set term ; ^ > > > > > > ALTER TABLE M_UNICODE ADD CONSTRAINT M_U PRIMARY KEY (S1, S2); > > > ALTER TABLE M_CI_AI ADD CONSTRAINT M_CI_AI PRIMARY KEY (S1, S2); > > > commit work; > > > > > > ALTER TABLE D_UNICODE ADD CONSTRAINT D_U FOREIGN KEY (S1, S2) > > REFERENCES > > > M_UNICODE (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE; > > > ALTER TABLE D_CI_AI ADD CONSTRAINT D_CI_AI FOREIGN KEY (S1, S2) > > > REFERENCES M_CI_AI (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE; > > > commit work; > > > > > > Query > > > ------------------------------------------------ > > > update m_ci_ai set s2 = 'C' where s1 = 'A' and s2 = 'B'; > > > > > > Operations > > > ------------------------------------------------ > > > Read : 9 > > > Writes : 0 > > > Fetches: 2 070 > > > Marks : 6 > > > > > > > > > Enchanced Info: > > > > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > > | Table Name | Records | Indexed | > > Non-Indexed | > > > Updates | Deletes | Inserts | Backouts | Purges | Expunges | > > > | | Total | reads | > > reads | > > > | | | | | | > > > > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > > |D_CI_AI | 0 | 2002 | > > 0 | > > > 1 | 0 | 0 | 0 | 0 | 0 | > > > |M_CI_AI | 0 | 2 | > > 0 | > > > 1 | 0 | 0 | 0 | 0 | 0 | > > > > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > > > > > Query > > > ------------------------------------------------ > > > update m_unicode set s2 = 'C' where s1 = 'A' and s2 = 'B'; > > > Operations > > > ------------------------------------------------ > > > Read : 0 > > > Writes : 0 > > > Fetches: 43 > > > Marks : 8 > > > > > > > > > Enchanced Info: > > > > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > > | Table Name | Records | Indexed | > > Non-Indexed | > > > Updates | Deletes | Inserts | Backouts | Purges | Expunges | > > > | | Total | reads | > > reads | > > > | | | | | | > > > > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > > |D_UNICODE | 0 | 2 | > > 0 | > > > 1 | 0 | 0 | 0 | 0 | 0 | > > > |M_UNICODE | 0 | 2 | > > 0 | > > > 1 | 0 | 0 | 0 | 0 | 0 | > > > > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > > > > > > > > I appreciate any help to solve this problem, thank you > > > > > > Best regards > > > > > > Luis Forra > > > --- > > > > > > > > > Firebird-Devel mailing list, web interface at > > https://lists.sourceforge.net/lists/listinfo/firebird-devel > > > > > > > > > -- > > Mark Rotteveel > > > > > > Firebird-Devel mailing list, web interface at > > https://lists.sourceforge.net/lists/listinfo/firebird-devel > > > > > > > > > > > > Firebird-Devel mailing list, web interface at > https://lists.sourceforge.net/lists/listinfo/firebird-devel > > > > > > Firebird-Devel mailing list, web interface at > https://lists.sourceforge.net/lists/listinfo/firebird-devel >
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
