maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Alexander!

On Oct 21, Alexander Barkov wrote:
> 
> On 10/20/21 2:48 AM, Sergei Golubchik wrote:
> >>   20000000-0000-0000-0000-000000000002
> >> +10000000-0000-0000-0000-000000000003
> >>   20000000-0000-0000-0000-000000000003
> > 
> > please, add a test for `ORDER BY CONCAT(a)` just to test the
> > lexicographical ordering of UUIDs. Or may be `ORDER BY HEX(a)` ?
> > Or `UNHEX` ? Whatever the recommended approach should be.
> 
> I propose to recommend using CAST(uuid AS BINARY(16)) for
> lexicographical order. This CAST only performs byte reordering
> (conversion from the in-record to in-memory format)
> 
> While CONCAT and HEX additionally involve BINARY(16) -> VARCHAR(32) 
> conversion through UUID::to_string().
> 
> The former should be faster.

Fine.

> >> diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_memory.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_memory.result
> >> index 42bb74d4b01..eb7a51895b9 100644
> >> --- a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_memory.result
> >> +++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_memory.result
> >> @@ -25,7 +25,7 @@ a
> >>   00000000-0000-0000-0000-0000000000ff
> >>   EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
> >>   id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> >> -1	SIMPLE	t1	ref	a	a	17	const	2	Using where
> >> +1	SIMPLE	t1	ref	a	a	17	const	4	Using where
> > 
> > why?
> 
> Hash collision now happens differently, this affects the optimizer 
> estimation for certain values.
...
> So it did not change to worse. Just a coincidence.

I hope so, although in all changed test results it did change to worse.
Not always doubled, but always increased.

> >> diff --git a/sql/sql_type_fixedbin.h b/sql/sql_type_fixedbin.h
> >> index c6e3d20bcfa..5141cb9fad4 100644
> >> --- a/sql/sql_type_fixedbin.h
> >> +++ b/sql/sql_type_fixedbin.h
> >> +  static ulong KEY_pack_flags(uint column_nr)
> >> +  {
> >> +    /*
> >> +      Return zero by default. A particular data type can override
> >> +      this method return some flags, e.g. HA_PACK_KEY to enable
> >> +      key prefix compression.
> > 
> > what if you change it later, will it make old tables corrupted?
> 
> I did not check.
> This method is used only in mysql_prepare_create_table().
> Then flags are stored inside FRM. In theory the change should not
> make old tables corrupted.

I think this too. Just thought it might be good to verify it. E.g.,
create a table, flip the flag, recompile and see if it opens.

> Your question made me also think what should be the default.
> Probably we should do it the other way around:
> - enable compression by default
> - disable compression in INET6
> What do you think?

Enable if NATIVE_LEN > 6? or >8 ?

Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx