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@mariadb.org